Search code examples
sqlpostgresqlpgadminpsql

PostgreSQL - Determining the Percentage of a Total

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 a % value from total to each row. 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 something like:

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

but as you know for sure, that 'number' must appear in GROUP BY or aggregate function so I can't use it. How to do that?

Solution

  • You could use sum with an over clause:

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

    Example at SQL Fiddle.