SQL query to group by age range from date created

Question

I want to get statistics with sql query. My table is like this:

ID MATERIAL CREATEDATE DEPARTMENT
1  M1       10.10.1980 D1
2  M2       11.02.1970 D2
2  M3       18.04.1971 D3
.....................
.....................
.....................

How can I get a range of data count like this

DEPARTMENT AGE<10  10<AGE<20 20<AGE
D1         24       123      324
D2         24       123      324

Answer 1

Assuming that CREATEDATE is a date column, in PostgreSQL you can use the AGE function:

select DEPARTMENT, age(CREATEDATE) as AGE
from Materials

and with date_part you can get the age in years. To show the data in the format that you want, you could use this GROUP BY query:

select
  DEPARTMENT,
  sum(case when date_part('year', age(CREATEDATE))<10 then 1 end) as "age<10",
  sum(case when date_part('year', age(CREATEDATE))>=10 and date_part('year', age(CREATEDATE))<20 then 1 end) as "10<age<20",
  sum(case when date_part('year', age(CREATEDATE))>=20 then 1 end) as "20<age"
from
  Materials
group by
  DEPARTMENT

which can be simplified as:

with mat_age as (
  select DEPARTMENT, date_part('year', age(CREATEDATE)) as mage
  from Materials
)
select
  DEPARTMENT,
  sum(case when mage<10 then 1 end) as "age<10",
  sum(case when mage>=10 and mage<20 then 1 end) as "10<age<20",
  sum(case when mage>=20 then 1 end) as "20<age"
from
  mat_age
group by
  DEPARTMENT;

if you are using PostgreSQL 9.4 you can use FILTER:

with mat_age as (
  select DEPARTMENT, date_part('year', age(CREATEDATE)) as mage
  from Materials
)
select
  DEPARTMENT,
  count(*) filter (where mage<10) as "age<10",
  count(*) filter (where mage>=10 and mage<20) as "10<age<20",
  count(*) filter (where mage>=20) as "20<age"
from
  mat_age
group by
  DEPARTMENT;