Re: how to use aggregate functions in this case

On Sun, Aug 25, 2013 at 8:36 PM, BladeOfLight16 <bladeoflight16(at)gmail(dot)com>wrote:

> This appears to be some kind of equal interval problem.
>
> SELECT v_rec1.user,
> WIDTH_BUCKET(v_rec_fts.lev, 0, 100, 4) AS bucket
> COUNT(*) as count,
> FROM v_rec2
> GROUP BY user, bucket;
>
> (Untested, but this should be the gist.)
>
> Bucket 1 would be 0 to 25, bucket 2 is 25 to 50, 3 is 50 to 75, 4 is 75 to
> 100. If you really need to change the bucket number to some kind of text,
> you can probably nest this query inside another that uses a CASE to pick
> the text based on on the bucket number.
>
> Good luck.
>

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;