2007/7/27, Carol Cheung <[EMAIL PROTECTED]>:
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
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
