Re: group by range of values - Mailing list pgsql-sql

On 7/27/07, I wrote:
> On 7/27/07, Carol Cheung <cacheung@consumercontact.com> wrote:
> > Something like:
> >
> > decade | average(salary)
> > -------+-----------------
> >    1940 |  69500
> >    1950 |  53333.33
> >    1960 |  53000
> >    1970 |  40333.33
>
> CREATE TABLE tester (
>   birth_year integer,
>   salary numeric(10,2)
> );
>
> SELECT
> SUBSTRING(TO_CHAR(BIRTH_YEAR,'FM9999') FROM 1 FOR 3)||'0'
> AS DECADE
> , AVG(SALARY) AS AVG_SALARY
> FROM TESTER
> GROUP BY
> SUBSTRING(TO_CHAR(BIRTH_YEAR,'FM9999') FROM 1 FOR 3)||'0'
> ORDER BY DECADE;

A bit simpler, if year is int:

SELECT
BIRTH_YEAR/10*10
AS DECADE
, AVG(SALARY) AS AVG_SALARY
FROM TESTER
GROUP BY
BIRTH_YEAR/10*10
ORDER BY DECADE;