SQL query to update ave_cost for entire category column

I have this table that needs to have (for every row) the average cost of that record and the average cost of the category in which it's in.

id      category   cost       num  ave_cost   ave_cost_category
15117012    15    357.00     420      0.85         0.85   
79030402    79    365.00     349      1.04         1.04  
90125402    90    351.20     439      0.80         0.828 
90125146    90    105.00     112      0.9375       0.828 

ave_cost_category is the column I need to co开发者_如何学JAVAmpute (it is currently null). I have ave_cost data in the table.

You could use a CTE (Common Table Expression) to calculate the average cost per category, and update your table from that:

;WITH CatCost AS
(
    SELECT
       Category,
       SUM(Cost) / (1.0 * SUM(Num)) 'AvgCostCat'
    FROM
       dbo.YourTable
    GROUP BY
       Category
)
UPDATE dbo.YourTable
SET ave_cost_category = cc.AvgCostCat
FROM CatCost
WHERE dbo.YourTable.Category = cc.Category

WITH t
     AS (SELECT SUM(cost) OVER (PARTITION BY category) AS c,
                SUM(num)  OVER (PARTITION BY category) AS n,
                *
         FROM   yourtable)
UPDATE t
SET    ave_cost = cost / num,
       ave_cost_category = c / n 

UPDATE MyTable
SET
   ave_cost=cost/num,
   ave_cost_category=t1.ave_cost_category
FROM MyTable
INNER JOIN
 (
   SELECT category,avg(ave_cost) as ave_cost_category
   FROM MyTable
   GROUP BY category
) t1 on MyTable.category = t1.category

The inner query in the JOIN calculates the average cost across the entire category.

I would suggest creating both as computed columns in the table.

create function dbo.fnAveCostCat(@category int)
returns decimal(10,3)
as
begin
    declare @ave decimal(10,3)
    select @ave = AVG(t.cost/t.num)
        from YourTable t
        where t.category = @category
    return @ave
end
go

alter table YourTable
    add ave_cost as cost/num,
        ave_cost_category as dbo.fnAveCostCat(category)
go

Perhaps this will help you:

 UPDATE yourTable SET ave_cost_category = x.avgcost FROM 
    yourTable inner JOIN (SELECT category, AVG(ave_cost) AS avgcost 
FROM yourTable GROUP BY category) x
    ON yourTable.category = x.category

to update your average cost, try

update mytable set ave_cost = cost / num;

for the average cost per category, i'm not sure this will work on sql server, but you can try :

update mytable set ave_cost_category = average
from mytable
inner join (
    select category, avg(ave_cost) as average
    from mytable
    group by category
) as averages on table.category = averages.category