PostgreSQL - determining the percentage of the total

Question

I have a table

ID | NUMBER ------|---------- 1 | 102 2 | 145 3 | 512 4 | 231 5 | 94

and I want to sum all "NUMBER" and return the% value of the total to each row. The result should look like this:

 ID | NUMBER | PERC ------|--------------|------- 1 | 102 | 9.4 2 | 145 | 13.4 3 | 512 | 47.2 4 | 231 | 21.3 5 | 94 | 8.7

So far I have had something like:

 SELECT (number/sum(number)*100) AS perc FROM mytable;

but, as you know for sure, this number should appear in a GROUP BY or aggregate function, so I cannot use it. How to do it?

+6

Marián Zeke Šedaj Oct 25 '12 at 10:59

1 answer

Andomar · Accepted Answer · 2012-10-25T11:00:46+0000

You can use sum with an over clause:

 SELECT 100.0 * number / sum(number) over () as perc FROM mytable;