Janek Sendrowski wrote
> Sorry, I formulated it wrong.
>
> My problem is, that I want to count the ranges for every user, but if I
> use count(range), it counts the ranges of all users.
Assuming your example output is indeed what you desire:
SELECT user, '0 to 25'::varchar AS percentage, count(*) FROM tbl WHERE val
BETWEEN 0 AND 25 GROUP BY user
UNION ALL
SELECT user, '25 to 50'::varchar, count(*) FROM tbl WHERE val BETWEEN 25 AND
50 GROUP BY user
...and so forth
Note that BETWEEN has inclusive end-points so anything with exactly 25 (for
example) is being double-counted.
Your other option is something like:
SELECT user, percentage_range_category, count(*) FROM (
SELECT recordid, user, CASE .... END::varchar AS
percentage_range_category
) categorize
GROUP BY user, percentage_range_category;
In short for every record you simply categorize the value then add that
category to your group-by.
Both are equally valid and the second one is probably easier to comprehend;
the first option just happened to occur to me first. I have no idea which
one would perform better in theory nor specifically with your data.
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/how-to-use-aggregate-functions-in-this-case-tp5768522p5768525.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
