From: Michael Glaesemann <grzm@seespotcode.net>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Carol Cheung <cacheung@consumercontact.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: group by range of values
Date: Fri, 27 Jul 2007 15:43:03 -0500
Message-ID: <6F7EBD6A-A1FD-44A8-B4E6-EA415DFA3535@seespotcode.net> (raw)
In-Reply-To: <162867790707271230u4258b9a9re0b672e948e44684@mail.gmail.com>
References: <46AA41EC.8050101@consumercontact.com>
<162867790707271230u4258b9a9re0b672e948e44684@mail.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
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: grzm@seespotcode.net, pavel.stehule@gmail.com, cacheung@consumercontact.com
Subject: Re: group by range of values
In-Reply-To: <6F7EBD6A-A1FD-44A8-B4E6-EA415DFA3535@seespotcode.net>
* 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