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_yearBETWEEN 1960 AND 1969 THEN 1960 WHEN birth_year BETWEEN 1970 AND 1979  THEN 1979 END, AVG(salary) FROM tester
GROUPBY 1 ORDER BY 1;
 

Regards
Pavel Stehule


2007/7/27, Carol Cheung <cacheung@consumercontact.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@postgresql.org so that your
>        message can get through to the mailing list cleanly
>