From: | Paul Jungwirth <pj(at)illuminatedcomputing(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Percent of Total in Histogram Query |
Date: | 2012-11-15 22:44:27 |
Message-ID: | CA+6hpakmz34Uzta4=rBJdtmOUWk9gKQR6b2t-JMke8ZvhZddaQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
Thanks,
Paul
--
_________________________________
Pulchritudo splendor veritatis.
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2012-11-15 22:44:46 | Re: High SYS CPU - need advise |
Previous Message | Vlad | 2012-11-15 22:31:29 | Re: High SYS CPU - need advise |