Re: how to use aggregate functions in this case

On Sun, Aug 25, 2013 at 5:59 PM, Janek Sendrowski <janek12(at)web(dot)de> wrote:

> SELECT v_rec1.user,
> sum(CASE WHEN v_rec_fts.lev BETWEEN 0 AND 25 THEN 1 ELSE 0 END) as
> "0 to 25",
> sum(CASE WHEN v_rec_fts.lev BETWEEN 25 AND 50 THEN 1 ELSE 0 END)
> as "25 to 50",
> sum(CASE WHEN v_rec_fts.lev BETWEEN 50 AND 100 THEN 1 ELSE 0 END)
> as "50 to 100"
> INTO v_rec2
> GROUP BY user;
>
> Now I want to summuarize the "0 to 25" values and the others in the same
> query.
> Somehow like this: count("0 to 25")
> But I want to do it with every single user and I don't know how to do that
>
> A result should look like this:
>
> user percentage count
> smith "0 to 25" 5
> smith "25 to 50" 7
> smith "50 to 75" 2
> jones "0 to 25" 11
> jones "25 to 50" 1
> jones "50 to 75" 3
>
>
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.