Re: group by range of values

Carol Cheung wrote:
> 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
>

Assuming birth_year is an integer (if it's not, then just change the
query to cast it to one before the division), one possible approach
might be:

select birth_year / 10 || '0' as decade,
avg(salary::numeric) as average_salary
from tester
group by decade
order by decade asc;

-Jon

--
Senior Systems Developer
Media Matters for America
http://mediamatters.org/