How to separate range of year on oracle

user_13052453 0
Viewed 54

I am working on a db oracle and I need to create a query where it return a range of date. For example:

Supose that I had a field of like this:

enter image description here

I need to get this dates and apply a range of years to return someting like:

|'0-5'|'6-10'|'11-15'|... 
| 10  |  35  |   20  |...

where each range contains a number of people in this range of years old.

I tried to use SELECT CASE...

SELECT CASE 
    WHEN DATE_BORN <= DATE_BORN + 5 THEN '0 - 5
    WHEN DATE_BORN >= DATE_BORN + 6 AND DATE_BORN <= 10 THEN '6 - 10'
        END AS AGE_RANGE,
    COUNT(*)
FROM MY_TABLE
GROUP BY 1

So I saw that this way change only days not year.

How can I write this query?

aggregation oracle sql
2 Answers

That's conditional aggregation:

SQL> with test (date_born) as
  2    (select date '2000-05-12' from dual union all
  3     select date '2001-05-12' from dual union all
  4     select date '2012-05-12' from dual union all
  5     select date '2013-05-12' from dual union all
  6     select date '2004-05-12' from dual union all
  7     select date '2008-05-12' from dual union all
  8     select date '2009-05-12' from dual union all
  9     select date '2001-05-12' from dual union all
 10     select date '2012-05-12' from dual union all
 11     select date '2001-05-12' from dual union all
 12     select date '2004-05-12' from dual union all
 13     select date '2005-05-12' from dual
 14    )
 15  select
 16    sum(case when extract (year from date_born) between 2000 and 2005 then 1 else 0 end) as "2000 - 2005",
 17    sum(case when extract (year from date_born) between 2006 and 2010 then 1 else 0 end) as "2006 - 2010",
 18    sum(case when extract (year from date_born) between 2011 and 2015 then 1 else 0 end) as "2011 - 2015"
 19  from test;

2000 - 2005 2006 - 2010 2011 - 2015
----------- ----------- -----------
          7           2           3

SQL>
user_90979060

Here is a dynamic way to do this (using the sample table above)

First I think it's easier to have your ranges in rows rather than columns, easier for having a variety of dates that may change.

Second your first grouping is 6 years, so I changed it to just be series of 5 years:

with test (date_born) as
 (select date '2000-05-12' from dual union all
  select date '2001-05-12' from dual union all
  select date '2012-05-12' from dual union all
  select date '2013-05-12' from dual union all
  select date '2004-05-12' from dual union all
  select date '2008-05-12' from dual union all
  select date '2009-05-12' from dual union all
  select date '2001-05-12' from dual union all
  select date '2012-05-12' from dual union all
  select date '2001-05-12' from dual union all
  select date '2004-05-12' from dual union all
  select date '2005-05-12' from dual
  )
  ,mydata AS (
SELECT 
  (SELECT min(extract(YEAR FROM date_born)) FROM test)+((LEVEL-1)*5)dt1 
  ,(SELECT min(extract(YEAR FROM date_born)) FROM test)+((LEVEL-1)*5)+4 dt2
FROM dual CONNECT BY LEVEL*5 <= 
  (SELECT max(extract(YEAR FROM date_born))-min(extract(YEAR FROM date_born)) FROM test)+5) 
SELECT d.*, count(t.date_born) cnt FROM mydata d 
LEFT JOIN test t ON extract(YEAR FROM date_born) BETWEEN d.dt1 AND d.dt2
GROUP BY dt1, dt2
ORDER BY dt1;

You get this for your solution

DT1     DT2     CNT
2000    2004    6
2005    2009    3
2010    2014    3

Solution is basically extracting years from dates, finding min/max of this data set, using connect to get all years in between, and then joining to count your matching records

user_60054510
Related