在Oracle中按2个日期分组

六羟甲基三聚氰胺六甲醚。…可以使用条件聚合,但需要生成月份列表:

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;

严格来说,如果所有月份都有数据,则不需要月份列表:

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;

干得好:

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');

编辑:trunc类型错误,它应该是trunc(开始日期,'mm')而不是trunc(开始日期)