Re: group by range of values

On 7/27/07, I wrote:
> On 7/27/07, Carol Cheung <cacheung(at)consumercontact(dot)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;