Re: group by range of values

Поиск
Список
Период
Сортировка
От Jon Sime
Тема Re: group by range of values
Дата
Msg-id 46AA46BA.9090404@mediamatters.org
обсуждение исходный текст
Ответ на group by range of values  (Carol Cheung <cacheung@consumercontact.com>)
Список pgsql-sql
Carol Cheung wrote:
> 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
> 

Assuming birth_year is an integer (if it's not, then just change the 
query to cast it to one before the division), one possible approach 
might be:
    select birth_year / 10 || '0' as decade,        avg(salary::numeric) as average_salary    from tester    group by
decade   order by decade asc;
 

-Jon

-- 
Senior Systems Developer
Media Matters for America
http://mediamatters.org/


В списке pgsql-sql по дате отправления:

Предыдущее
От: Carol Cheung
Дата:
Сообщение: group by range of values
Следующее
От: "Pavel Stehule"
Дата:
Сообщение: Re: group by range of values