DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=FJI+6FEzqSA/GMvcc2tDPjGl8+FRSMpQA2GUb+W96CsGXllHRgyYgAbUE9cR5aotwJJ/Nn4ufpMOzsA40J8m+DUvSfIY91wYNBqjojbQ1243CQ2lXeysRb9xcH7EBZv3ZJl5IK2t5+sikTKtxBhxFAJTqGsLxgljst9AJZEnOZs=
Message-ID: <a55915760707271231w15d18836me4c3c4f8685e142a@mail.gmail.com>
Date: Fri, 27 Jul 2007 14:31:32 -0500
From: "=?UTF-8?Q?Rodrigo_De_Le=C3=B3n?=" <rdeleonp@gmail.com>
To: "Carol Cheung" <cacheung@consumercontact.com>
Subject: Re: group by range of values
Cc: pgsql-sql@postgresql.org
In-Reply-To: <a55915760707271228l76711711l1ae9e033db22e9d9@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
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;