CODEWITHSUNDEEP
mysqlsql
dirk
dirk

Reputation: 2306

MySQL: Calculation based on SUM in update query

My SQL table looks like this, it has about 500 records and the volume_percentage is currently empty:

id  | volume | volume_percentage
--------------------------------
1   | 3151   | 0.00
2   | 832    | 0.00
3   | 39011  | 0.00
[..]
499 | 2311   | 0.00
500 | 1201   | 0.00

I want to populate the volume_percentage field of each record. The volume percentage should reflect the % of volume this record has compared to the total.

So basically:

volume_percentage = volume / SUM(volume) * 100;

Using PHP and a while loop of 500 queries makes this easily possible, but that doesn't sound really optimal to me (this is a process that needs to be done on a regular basis, as the volumes often change). Is there a way to do this in pure SQL?

Upvotes: 0

Views: 31

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You can do this with a join and aggregation:

update mytable t cross join
       (select sum(volume) as sumvolume
        from mytable
       ) s
    set volume_percentage = 100 * volume / s.sumvolume;

Upvotes: 1

Related Questions