Geek asks and answers

SQL query to update ave_cost for an entire category column

I have this table, which should have (for each row) the average cost of this record and the average cost of the category in which it is located.

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 compute (it is currently null). I have ave_cost data in a table.

+3
source share
6 answers

You can use CTE (Common Table Expression) to calculate the average cost of each category and update the table:

;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
+1
source 
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 JOIN internal query calculates the average cost for the entire category.

+1
source 
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
+1

.

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
+1

, :

 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
0

,

update mytable set ave_cost = cost / num;

for the average cost of each category, I'm not sure if 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
-1
source

More articles:


All Articles