Re: Percent of Total in Histogram Query

From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Paul Jungwirth
Sent: Thursday, November 15, 2012 5:44 PM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] Percent of Total in Histogram Query

Hello,

I'd like to write a histogram-like query that shows these columns:
- x-value from 0 to k.
- number of rows with that x-value.
- number of rows seen so far (i.e. with the current x-value or less).
- % of total rows seen so far.

The following query works for the first three columns, but the last column gives me an error:

SELECT c,
COUNT(*) AS items_count,
SUM(COUNT(*)) OVER (ORDER BY c) AS total_items_count,
SUM(COUNT(*)) OVER (ORDER BY c) / SUM(COUNT(*)) AS total_items_perc
FROM (SELECT p.id, COUNT(*) c
FROM parent p, child ch
WHERE p.id = ch.parent_id
GROUP BY p.id
) x
GROUP BY x.c
ORDER BY x.c

Including that fourth SELECT column gives me this error:

ERROR: aggregate function calls cannot be nested

Is there any way to get % of total in a query like this?

Yes. Use a sub-query.

In this case modify the fourth column to be: SUM(COUNT(*)) OVER () AS grandtotal_items_count

Then in a new query layer you can write:

total_items_count / grandtotal_items_count AS total_items_perc

I would also rename “total_items_count” to something like “runningtotal_items_count”

David J.