CODEWITHSUNDEEP
mysqlsqlsql-update
Jimmy
Jimmy

Reputation: 61

Select SUM and Calculate Percentage per row

How do I select the SUM of all the values from C1, and then UPDATE the values of C2 as percentage per row

C2 = C1 / SUM(C1)

+----+-------+
| C1 |   C2  |
+----+-------+
|  1 |   2%  |
|  2 |   5%  |
|  3 |   8%  |
|  4 |   11% |
|  5 |   13% |
|  6 |   16% |
|  7 |   19% |
|  8 |   22% |
+----+-------+

Upvotes: 1

Views: 62

Answers (1)

forpas
forpas

Reputation: 164139

Cross join the table to the sum of column c1:

update tablename t cross join (
  select sum(c1) total from tablename  
) s
set t.c2 = concat(floor(100.0 * t.c1 / s.total), '%');

See the demo.
Results:

| c1  | c2  |
| --- | --- |
| 1   | 2%  |
| 2   | 5%  |
| 3   | 8%  |
| 4   | 11% |
| 5   | 13% |
| 6   | 16% |
| 7   | 19% |
| 8   | 22% |

Upvotes: 4

Related Questions