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=W6gHgQmDaBwl7WwKjpG7vFzrEF4We8Cq6S6KC4IGwDbFOZz2f2uDt//STABMhiivk7sq/iYHKSB39/RF6T3sFWuh2sthc9oT1F9aKkoPEGdAO0BkzPbIMx1iyTMTo4H/zwI0lOJVdRMXPbrbitZUw8c5fIi7G8cHgvpmKaf44RY=
Message-ID: <162867790707271230u4258b9a9re0b672e948e44684@mail.gmail.com>
Date: Fri, 27 Jul 2007 21:30:23 +0200
From: "Pavel Stehule" <pavel.stehule@gmail.com>
To: "Carol Cheung" <cacheung@consumercontact.com>
Subject: Re: group by range of values
Cc: pgsql-sql@postgresql.org
In-Reply-To: <46AA41EC.8050101@consumercontact.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <46AA41EC.8050101@consumercontact.com>

Hello

you can use CASE like

SELECT CASE
  WHEN birth_year BETWEEN 1940 AND 1949 THEN 1940
  WHEN birth_year BETWEEN 1950 AND 1959 THEN 1950
  WHEN birth_year BETWEEN 1960 AND 1969 THEN 1960
  WHEN birth_year BETWEEN 1970 AND 1979  THEN 1979 END, AVG(salary)
  FROM tester GROUP BY 1 ORDER BY 1;

Regards
Pavel Stehule


2007/7/27, Carol Cheung <cacheung@consumercontact.com>:
> 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
>
> Thanks in advance,
> C
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>