CODEWITHSUNDEEP
sqlpostgresqlaggregate-functionspercentage
AvocadoRivalry
AvocadoRivalry

Reputation: 411

% of total calculation without subquery in Postgres

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.

Upvotes: 2

Views: 1002

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656381

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.

Related answer:

Key to understanding why this works is the sequence of evens in a SELECT query:

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269543

Use window functions:

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

Upvotes: 5

Related Questions