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/