From: Pavel Stehule <pavel.stehule@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 21:30:23 +0200
Message-ID: <162867790707271230u4258b9a9re0b672e948e44684@mail.gmail.com> (raw)
In-Reply-To: <46AA41EC.8050101@consumercontact.com>
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
>
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: pavel.stehule@gmail.com, cacheung@consumercontact.com
Subject: Re: group by range of values
In-Reply-To: <162867790707271230u4258b9a9re0b672e948e44684@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