Re: group by range of values

Hello

you can use CASE like

SELECT CASE
WHEN birth_year BETWEEN 1940 AND 1949 THEN 1940
WHEN birth_year BETWEEN 1950 AND 1959 THEN 1950
WHEN birth_year BETWEEN 1960 AND 1969 THEN 1960
WHEN birth_year BETWEEN 1970 AND 1979 THEN 1979 END, AVG(salary)
FROM tester GROUP BY 1 ORDER BY 1;

Regards
Pavel Stehule

2007/7/27, Carol Cheung <cacheung(at)consumercontact(dot)com>:
> Hello,
>
> Here's my table:
>
> db=# select * from tester order by birth_year;
> birth_year | salary
> ------------+--------
> 1946 | 78000
> 1949 | 61000
> 1951 | 58000
> 1953 | 56000
> 1958 | 52000
> 1962 | 50000
> 1965 | 45000
> 1967 | 60000
> 1968 | 57000
> 1970 | 47000
> 1972 | 32000
> 1973 | 42000
> (12 rows)
>
> How can I display the average salary grouped by decade of birth year?
> That is, is it possible to display the average salary of those born in
> the 1940's, the average salary of those born in the 1950's, average
> salary of those born in the 1960's, and those born in the 1970's, all in
> one result table?
> Something like:
>
> decade | average(salary)
> -------+-----------------
> 1940 | 69500
> 1950 | 53333.33
> 1960 | 53000
> 1970 | 40333.33
>
> Thanks in advance,
> C
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>