Question 90309 in PostgreSQL Question posted in
The official documentation can be found here.

Calculate percentage – Postgresql

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

Tags:

3

Answers


  1. 0

    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);
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
  3. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search