In-Reply-To: <162867790707271230u4258b9a9re0b672e948e44684@mail.gmail.com>
References: <46AA41EC.8050101@consumercontact.com>
	<162867790707271230u4258b9a9re0b672e948e44684@mail.gmail.com>
Mime-Version: 1.0 (Apple Message framework v752.2)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <6F7EBD6A-A1FD-44A8-B4E6-EA415DFA3535@seespotcode.net>
Cc: "Carol Cheung" <cacheung@consumercontact.com>,
 pgsql-sql@postgresql.org
Content-Transfer-Encoding: 7bit
From: Michael Glaesemann <grzm@seespotcode.net>
Subject: Re: group by range of values
Date: Fri, 27 Jul 2007 15:43:03 -0500
To: "Pavel Stehule" <pavel.stehule@gmail.com>

2007/7/27, Carol Cheung <cacheung@consumercontact.com>:

> db=# select * from tester order by birth_year;
>   birth_year | salary
> ------------+--------
>         1946 |  78000
>         1949 |  61000

What is the data type of the birth_year column? I'd suggest using  
date if you can, as what it is is a date with year precision. You  
can't specify such a precision, but you can decide that all  
birth_year's will have month and year of January 1 (enforced by a  
CHECK constraint, if you wish), or you could just choose to ignore  
the month and year part in your calculations.

> 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


Here's an example:

CREATE TABLE salaries (birth_year DATE PRIMARY KEY, salary NUMERIC  
NOT NULL);

INSERT INTO salaries (birth_year, salary) VALUES
     ('1946-01-01',78000), ('1949-01-01',61000), ('1951-01-01',58000)
     , ('1953-01-01',56000), ('1958-01-01',52000), ('1962-01-01',50000)
     , ('1965-01-01',45000), ('1967-01-01',60000), ('1968-01-01',57000)
     , ('1970-01-01',47000), ('1972-01-01',32000), ('1973-01-01',42000);

SELECT birth_decade, AVG(salary)
FROM (
     SELECT birth_year
         , date_trunc('decade', birth_year)::date as birth_decade
         , salary
     FROM salaries) as salaries_with_decades
GROUP BY birth_decade
ORDER BY birth_decade;
birth_decade |        avg
--------------+--------------------
1940-01-01   | 69500.000000000000
1950-01-01   | 55333.333333333333
1960-01-01   | 53000.000000000000
1970-01-01   | 40333.333333333333
(4 rows)

If birth_year is an integer column, here's another way to do it,  
taking advantage of the fact that integer division truncates.

CREATE TABLE salaries (birth_year INTEGER PRIMARY KEY, salary NUMERIC  
NOT NULL);

INSERT INTO salaries (birth_year, salary) VALUES
     (1946,78000), (1949,61000), (1951,58000), (1953,56000),  
(1958,52000)
     , (1962,50000), (1965,45000), (1967,60000), (1968,57000),  
(1970,47000)
     , (1972,32000), (1973,42000);

SELECT birth_decade, AVG(salary)
FROM (
     SELECT birth_year
         , birth_year / 10 * 10 as birth_decade
         , salary
     FROM salaries) as salaries_with_decades
GROUP BY birth_decade
ORDER BY birth_decade;
birth_decade |        avg
--------------+--------------------
          1940 | 69500.000000000000
          1950 | 55333.333333333333
          1960 | 53000.000000000000
          1970 | 40333.333333333333
(4 rows)

Hope this gives you some options.

Michael Glaesemann
grzm seespotcode net