SQL query for a group by age range from creation date

Question

I want to get statistics using sql query. My table is as follows:

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 for example

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

Answer 1

Assuming 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 display the data in the desired format, you can 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;

Answer 2

, CREATEDATE Postgres. , , , .

SELECT DEPARTMENT,
    SUM(CASE WHEN DATEDIFF(year, CREATEDATE, now()::date) < 10 THEN 1 ELSE 0 END) AS "AGE<10",
    SUM(CASE WHEN DATEDIFF(year, CREATEDATE, now()::date) >= 10 AND
             DATEDIFF(year, CREATEDATE, now()::date) < 20 THEN 1 ELSE 0 END) AS "10<AGE<20",
    SUM(CASE WHEN DATEDIFF(year, CREATEDATE, now()::date) >= 20 THEN 1 ELSE 0 END) AS "20<AGE"
FROM Materials
GROUP BY DEPARTMENT

Answer 3

You can use extract(year FROM age(createdate))to get the exact age.

ie

select extract(year FROM age(timestamp '01-01-1989')) age

will provide you

Result:

age
---
 27

so that you can use the following select statement to get the desired result:

 SELECT dept
    ,sum(CASE  WHEN age < 10THEN 1 END) "age<10"
    ,sum(CASE  WHEN age >= 10  AND age < 20 THEN 1 END) "10<age<20"
    ,sum(CASE WHEN age >= 20 THEN 1 END) "20<age"
FROM (
    SELECT dept,extract(year FROM age(crdate)) age
    FROM dt
    ) t
GROUP BY dept

If you do not want to use sub-selections, use this.

SELECT dept
    ,sum(CASE  WHEN extract(year FROM age(crdate)) < 10THEN 1 END) "age<10"
    ,sum(CASE  WHEN extract(year FROM age(crdate)) >= 10  AND extract(year FROM age(crdate)) < 20 THEN 1 END) "10<age<20"
    ,sum(CASE WHEN extract(year FROM age(crdate)) >= 20 THEN 1 END) "20<age"
FROM dt
GROUP BY dept