From: Rodrigo De León <rdeleonp@gmail.com>
To: Carol Cheung <cacheung@consumercontact.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: group by range of values
Date: Fri, 27 Jul 2007 14:31:32 -0500
Message-ID: <a55915760707271231w15d18836me4c3c4f8685e142a@mail.gmail.com> (raw)
In-Reply-To: <a55915760707271228l76711711l1ae9e033db22e9d9@mail.gmail.com>
References: <46AA41EC.8050101@consumercontact.com>
<a55915760707271228l76711711l1ae9e033db22e9d9@mail.gmail.com>
On 7/27/07, I wrote:
> On 7/27/07, Carol Cheung <cacheung@consumercontact.com> wrote:
> > Something like:
> >
> > decade | average(salary)
> > -------+-----------------
> > 1940 | 69500
> > 1950 | 53333.33
> > 1960 | 53000
> > 1970 | 40333.33
>
> CREATE TABLE tester (
> birth_year integer,
> salary numeric(10,2)
> );
>
> SELECT
> SUBSTRING(TO_CHAR(BIRTH_YEAR,'FM9999') FROM 1 FOR 3)||'0'
> AS DECADE
> , AVG(SALARY) AS AVG_SALARY
> FROM TESTER
> GROUP BY
> SUBSTRING(TO_CHAR(BIRTH_YEAR,'FM9999') FROM 1 FOR 3)||'0'
> ORDER BY DECADE;
A bit simpler, if year is int:
SELECT
BIRTH_YEAR/10*10
AS DECADE
, AVG(SALARY) AS AVG_SALARY
FROM TESTER
GROUP BY
BIRTH_YEAR/10*10
ORDER BY DECADE;
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: rdeleonp@gmail.com, cacheung@consumercontact.com
Subject: Re: group by range of values
In-Reply-To: <a55915760707271231w15d18836me4c3c4f8685e142a@mail.gmail.com>
* 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