Re: how to use aggregate functions in this case

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.