Oracle SQL - Group By Month for Past 12 Months, Fill in Data Gaps

Question

I've seen number of posts similar to this, but none quite fit for the scenario I'm working with. Attempting to query a DB and aggregate results for each month in the past 12. If no results exist for a particular month, I would like a 0 in a row for that particular month.

Running this query results in data for Nov 2016 - March 2017, with no results before/after:

SELECT SUM(PRICE) SumPrice, COUNT(*) TotalNum, EXTRACT(MONTH FROM SALE_DATE) Mo, EXTRACT(YEAR FROM SALE_DATE) Yr
FROM SALES_HIST
WHERE SALE_DATE BETWEEN TRUNC(ADD_MONTHS((LAST_DAY(SYSDATE)+1),-12)) 
AND LAST_DAY(SYSDATE)
AND ITEMNO = 'ABCD'
GROUP BY EXTRACT(MONTH FROM SALE_DATE), EXTRACT(YEAR FROM SALE_DATE)
ORDER BY Yr, Mo;

After some searching, I came up with this query to generate a list of the past 12 months on the fly:

SELECT
EXTRACT(MONTH FROM 
ADD_MONTHS(TRUNC(ADD_MONTHS((LAST_DAY(SYSDATE)+1),-12)), LEVEL - 1)) calMo,
EXTRACT(YEAR FROM 
ADD_MONTHS(TRUNC(ADD_MONTHS((LAST_DAY(SYSDATE)+1),-12)), LEVEL - 1)) calYr
FROM DUAL
CONNECT BY LEVEL <= 12;

which I believe I should be able to use within my query as a temporary table to fill in the data gaps. This is what I've been tinkering with:

WITH cal AS (
SELECT
  EXTRACT(MONTH FROM ADD_MONTHS(TRUNC(ADD_MONTHS((LAST_DAY(SYSDATE)+1),-12)), LEVEL - 1)) calMo,
  EXTRACT(YEAR FROM ADD_MONTHS(TRUNC(ADD_MONTHS((LAST_DAY(SYSDATE)+1),-12)), LEVEL - 1)) calYr
FROM DUAL
CONNECT BY LEVEL <= 12
)
select cal.calMo, cal.calYr, NVL(count(*), 0)
from cal
left join SALES_HIST sh on cal.calMo = EXTRACT(MONTH FROM sh.SALE_DATE)
WHERE SALE_DATE BETWEEN TRUNC(ADD_MONTHS((LAST_DAY(SYSDATE)+1),-12)) AND LAST_DAY(SYSDATE)
AND ITEMNO = 'ABCD'
group by cal.calMo, cal.calYr
order by cal.calYr, cal.calMo;

but this seems to return the same results as the initial query. I assume I'm missing something with the join, but not sure exactly. Any help would be greatly appreciated. Thanks!

can you post the data of the SALES_HIST table and the result of the last SQL please? — Ted at ORCL.Pro

Ferdinand Gaspar Ferdinand Gaspar · Accepted Answer · 2017-09-19T00:11:35

When you use WHERE clause it filter out all the resulting rows of the LEFT JOIN not met by the condition in WHERE clause, thus all the NULL values will be removed. If you use AND within the condition on your sales_hist table, it will filter those records first before creating the NULL values that didn't match the months/years in cal table. Also, use sh.itemno on the count instead of * because it will count each entry in the cal table which will have 1 on each month.

SELECT cal.calmo, 
       cal.calyr, 
       NVL(COUNT(sh.itemno), 0) /*changed from * to sh.itemno*/
  FROM cal
  LEFT JOIN sales_hist sh 
    ON cal.calMo = EXTRACT(MONTH FROM sh.sale_date)
   AND sale_date BETWEEN TRUNC(ADD_MONTHS((LAST_DAY(SYSDATE)+1),-12))
                     AND LAST_DAY(SYSDATE) /*changed from WHERE to AND*/
   AND itemno = 'ABCD'
 GROUP BY cal.calmo, 
          cal.calyr
 ORDER BY cal.calyr, 
          cal.calmo;

Sample data

CREATE TABLE sales_hist AS
SELECT TO_DATE('09172017','MMDDYYYY') sale_date,
       'ABCD' itemno
  FROM dual
 UNION ALL
SELECT TO_DATE('10172016','MMDDYYYY') sale_date,
       'ABCD' itemno
  FROM dual
 UNION ALL
SELECT TO_DATE('10182016','MMDDYYYY') sale_date,
       'ABCD' itemno
  FROM dual
   UNION ALL
SELECT TO_DATE('09172016','MMDDYYYY') sale_date,
       'ABCD' itemno
  FROM dual
 ;

Sample result. Note that 09172016 is not included in the count.

CALMO   CALYR   NVL(COUNT(SH.ITEMNO),0)
10      2016    2
11      2016    0
12      2016    0
1       2017    0
2       2017    0
3       2017    0
4       2017    0
5       2017    0
6       2017    0
7       2017    0
8       2017    0
9       2017    1

Oracle SQL - Group By Month for Past 12 Months, Fill in Data Gaps

1 Answers