Message-ID: <46AA46BA.9090404@mediamatters.org>
Date: Fri, 27 Jul 2007 15:25:46 -0400
From: Jon Sime <jsime@mediamatters.org>
Organization: Media Matters for America
User-Agent: Thunderbird 2.0.0.4 (X11/20070615)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: group by range of values
References: <46AA41EC.8050101@consumercontact.com>
In-Reply-To: <46AA41EC.8050101@consumercontact.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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/