[Solved] Last 6 months aggregation

by


You can try with something like this:

select TO_CHAR(CALLDATE,'yyyymm'),count(*)
from yourTable
--filter last n months (in this case 7)
where CALLDATE> SYSDATE - INTERVAL '7' MONTH
--group by month
group by TO_CHAR(CALLDATE,'yyyymm');

If you need zeros for months without a call:

WITH MONTH_COUNTER AS (
  SELECT LEVEL-1 AS ID 
  FROM DUAL 
  CONNECT BY LEVEL <= 6
), all_month as ( 
  --last 6 months
  SELECT TO_CHAR(ADD_MONTHS(sysdate, -ID),'yyyymm') m FROM MONTH_COUNTER
), all_count as (
  --previous query
  select TO_CHAR(CALLDATE,'yyyymm') m,count(*) c
  from yourTable
  where CALLDATE> SYSDATE - INTERVAL '7' MONTH
  group by TO_CHAR(CALLDATE,'yyyymm')
)
--nvl = when count(*) is null then put 0
select all_month.m,nvl(all_count.c,0)
from all_month
left outer join all_count
on all_month.m = all_count.m;

1

solved Last 6 months aggregation