CODEWITHSUNDEEP
sqlsql-server
user2101399
user2101399

Reputation: 1

Update row based on a group measure

[UNIT] [PRICE] [QUANTITY] [REVENUE]     [PERCENTAGE]
A      $10     25         $250           25%
B      $5      30         $150           15%
C      $20     30         $600           60%
Total Revenue             $1,000             100%

I'm trying to create a query to calculate the [PERCENTAGE] column, here is what I used:

UPDATE Table
SET [PERCENTAGE] = (PRICE*QUANTITY)/(sum(PRICE*QUANTITY)

Here is the error message:

An aggregate may not appear in the set list of an UPDATE statement.

Any Suggestion?

Thank you for reading.

Upvotes: 0

Views: 59

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269593

I would suggest doing this with window functions and a with clause:

with toupdate as (
      select t.*,
             sum(price*quantity) over () as total
      from t
     )
update toupdate
    set percentage = price*quantity / total;

Upvotes: 0

Explosion Pills
Explosion Pills

Reputation: 191729

UPDATE
    t1 target
    CROSS JOIN t1 src
SET
    target.PERCENTAGE = target.PRICE * target.QUANTITY / 
(SELECT SUM(src.PRICE * src.QUANTITY))

Upvotes: 2

Related Questions