Re: how to use aggregate functions in this case - Mailing list pgsql-general

BladeOfLight16 wrote
> Then again, I guess you don't need a nested query.
>
> SELECT  v_rec1.user,
>         CASE WIDTH_BUCKET(v_rec_fts.lev, 0, 100, 4)
>               WHEN 1 THEN '0 to 25'
>               WHEN 2 THEN '25 to 50'
>               WHEN 3 THEN '50 to 75'
>               WHEN 4 THEN '75 to 100'
>               ELSE 'But how?'
>         END CASE AS quarter_percentage
>          COUNT(*) as count,
> FROM v_rec2
> GROUP BY user, quarter_percentage;

This is clean but requires the use of equal intervals.

Another option, though I am unfamiliar with the exact syntax, is to use the
contains operator and an "intrange" (range type, for integer or whatever
actual type is needed).

SELECT CASE WHEN val @> '(0, 25]' -- '(25,50]', etc...

This allows for uneven bucket sizes and avoid the double-inclusive endpoints
problem that results from using BETWEEN.

Requires 9.2

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/how-to-use-aggregate-functions-in-this-case-tp5768522p5768573.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.