Calculate percentage-CodePudding

I would like to update the below mentioned table, like

rate = round(score / sum(score) * 100, 2)

 ------- ------ 
| score | rate |
 ------- ------ 
|    49 |    0 |
|    27 |    0 |
|    26 |    0 |
|    28 |    0 |
|     7 |    0 |
|     6 |    0 |
|     7 |    0 |
|    13 |    0 |
|    12 |    0 |
|    13 |    0 |
|    13 |    0 |
|     3 |    0 |
|     6 |    0 |
|    13 |    0 |
|     5 |    0 |
|     5 |    0 |
|    10 |    0 |
|   707 |    0 |
 ------- ------

and the outcome should be something like this

 ------- ------- 
| score | rate  |
 ------- ------- 
|    49 | 5.16  |
|    27 | 2.84  |
|    26 | 2.74  |
|    28 | 2.95  |
|     7 | 0.74  |
|     6 | 0.63  |
|     7 | 0.74  |
|    13 | 1.37  |
|    12 | 1.26  |
|    13 | 1.37  |
|    13 | 1.37  |
|     3 | 0.32  |
|     6 | 0.63  |
|    13 | 1.37  |
|     5 | 0.53  |
|     5 | 0.53  |
|    10 | 1.05  |
|   707 | 74.42 |
 ------- -------

I tried with this and and some other code, but the outcome is 0

update table1 
set rate = round((score / (select sum(score) 
                           from table1) * 100)::numeric, 2);

Here is the link of demo table

CodePudding user response：

You need to cast your sum as numeric to force numeric division

update table1 set rate=round((score::numeric/(select sum(score) from table1)*100)::numeric,2);

CodePudding user response：

Try this :

SELECT score
     , round((score :: numeric / sum(score) OVER ()) * 100, 2)
  FROM your_table

if score is an integer, then it must be casted as numeric or real or double precision so that to not get 0 as a result.

sum must be used as a window function here so that to calculate the sum on all the existing rows.

If you want to update the rate column of your_table :

UPDATE your_table t1
   SET rate = t2.rate
  FROM 
     ( SELECT score, round((score :: numeric / sum(score) OVER ()) * 100, 2) AS rate
         FROM your_table
     ) AS t2
 WHERE t1.score = t2.score

You need here the FROM clause because UPDATE doesn't accept aggregate and window functions in the SET clause.

see dbfiddle

CodePudding user response：

You can also use CTE with window functions;

WITH my_rates AS (
  SELECT DISTINCT
      score, 
      (score::NUMERIC * 100 / sum(score::NUMERIC) over())::NUMERIC(4,2) rate
    FROM table1
)
UPDATE table1 t SET 
    rate = my_rates.rate
  FROM my_rates
  WHERE
    my_rates.score = t.score

Here is the fiddle