% of total calculation without subquery in Postgres

Question

I'm trying to create a "Percentage of Total" column and currently using a subquery with no issues:

SELECT ID, COUNT(*), COUNT(*) / (SELECT COUNT(*)
FROM DATA) AS % OF TOTAL FROM DATA GROUP BY ID;

|  ID  | COUNT | % OF TOTAL |
|  1   |  100  |    0.10    |
|  2   |  800  |    0.80    |
|  3   |  100  |    0.10    |

However, for reasons outside the scope of this question, I'm looking to see if there is any way to accomplish this without using a subquery. Essentially, the application uses logic outside of the SQL query to determine what the WHERE clause is and injects it into the query. That logic does not account for the existence of subqueries like the above, so before going back and rebuilding all of the existing logic to account for this scenario, I figured I'd see if there's another solution first.

I've tried accomplishing this effect with a window function, but to no avail.

You can use windowing like `count(*) over ()` - assuming you are actually working on grouped data. However, if this is a performance critical query, you probably want to go with the subquery anyway. — Luaan, Mar 10 '15 at 15:10

score 5 · Accepted Answer · answered Mar 10 '15 at 15:08

5

Use window functions:

SELECT ID, COUNT(*),
       COUNT(*) / SUM(COUNT(*)) OVER () AS "% OF TOTAL"
FROM DATA
GROUP BY ID;

answered Mar 10 '15 at 15:08

Gordon Linoff

1,242,037
58
646
786

Great, the outer-wrapper on the `COUNT(*)` was exactly what I needed. Would you mind explaining why exactly that wrapper is necessary? – AvocadoRivalry Mar 10 '15 at 15:11
In an aggregation query, you can sum the results of aggregations. This syntax looks awkward at first, but it really makes sense. The `count(*)` is defined for each row. The `sum( . . .) over ()` sums the argument over all the rows. – Gordon Linoff Mar 10 '15 at 15:14

score 2 · Answer 2 · edited May 23 '17 at 12:06

SELECT id, count(*) AS ct
     , round(count(*)::numeric
           / sum(count(*)) OVER (ORDER BY id), 2) AS pct_of_running_total
FROM   data
GROUP  BY id;

You must add ORDER BY to the window function or the order of rows is arbitrary. I may seem correct at first, but that can change any time and without warning. It seems you want to order rows by id.

And you obviously don't want integer division, which would truncate fractional digits. I cast to numeric and round the result to two fractional digits like in your result.

% of total calculation without subquery in Postgres

2 Answers2