CODEWITHSUNDEEP
sqloracle-database
bd528
bd528

Reputation: 886

Grouping by 2 dates in Oracle

I have the following sample data in a table :-

Property_ID Start_Date  Status  Sale_Date
110         01/01/2017  Sold    01/02/2017
111         01/01/2017  Sold    05/02/2017
112         01/01/2017  Sold    31/12/2017
113         01/01/2017  Sold    31/12/2017
114         01/02/2017  Sold    18/04/2017
115         01/02/2017  Sold    18/04/2017
116         01/02/2017  Sold    18/12/2017

On Oracle is it possible to output the data based on the Start_date and the Sale_Date together, where the Sale_Date is less than 6 months from the Start_Date? So the expected output for the sample data would be :-

        Active  Six_months
Jan 17  4       2
Feb 17  3       2

Upvotes: 0

Views: 48

Answers (2)

hakobot
hakobot

Reputation: 186

Here you go:

Select trunc(Start_Date,'MM'),
       count(*) as "Active",
       SUM(case when MONTHS_BETWEEN(Sale_Date,Start_Date) <= 6 THEN 1 ELSE 0) as Six_months
From Table
Group by trunc(start_date,'MM');

EDIT: Wrong type of trunc, it should be trunc(Start_Date,'MM') instead of trunc(Start_Date)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269543

Hmmm . . . You can use conditional aggregation, but you need to generate the list of months:

select to_char(month_start, 'YYYY-MM') as yyyymm,
       count(t.property_id) as num_active,
       sum(case when sale_date < add_months(start_date, 6) then 1 else 0
           end) as num_sold_within_six_months
from (select date '2017-01-01' as month_start, date '2017-02-01' as month_end from dual union all
      select date '2017-02-01' as as month_start, date '2017-03-01' as month_end from dual
     ) d left join
     t
     on start_date >= month_start and start_date < month_end
group by to_char(sale_date, 'YYYY-MM')
order by yyyymm;

Strictly speaking, you don't need the list of months, if all months have data:

select to_char(sale_date, 'YYYY-MM') as yyyymm,
       count(t.property_id) as num_active,
       sum(case when sale_date < add_months(start_date, 6) then 1 else 0
           end) as num_sold_within_six_months
from t
group by to_char(sale_date, 'YYYY-MM')
order by yyyymm;

Upvotes: 2

Related Questions