Re: how to use aggregate functions in this case

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.