From: Jon Sime <jsime@mediamatters.org>
To: pgsql-sql@postgresql.org
Subject: Re: group by range of values
Date: Fri, 27 Jul 2007 15:25:46 -0400
Message-ID: <46AA46BA.9090404@mediamatters.org> (raw)
In-Reply-To: <46AA41EC.8050101@consumercontact.com>
References: <46AA41EC.8050101@consumercontact.com>
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jsime@mediamatters.org
Subject: Re: group by range of values
In-Reply-To: <46AA46BA.9090404@mediamatters.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox