Oracle SQL – Sum and group data by daily, weekly and monthly. (With missing date, week, and month)

I need to get total amount of transactions group by daily, weekly and monthly. What i’ve tried gave me the result I expected but not giving the date or week or month if there is no transactions on the particular period.

Daily

SELECT to_char(tran_timestamp, 'YYYY/MM/DD') FROM TRANSACTIONS
WHERE
TRAN_STATUS = 'SUCCESS' AND
tran_timestamp  >= TO_DATE('2019/07/01', 'yyyy/mm/dd')
AND tran_timestamp <= TO_DATE('2019/09/30','yyyy/mm/dd')
GROUP BY to_char(TRAN_TIMESTAMP, 'YYYY/MM/DD')
ORDER BY to_char(TRAN_TIMESTAMP, 'YYYY/MM/DD');

Weekly

SELECT to_char(tran_timestamp, 'YYYY/MM/W') FROM TRANSACTIONS
WHERE
TRAN_STATUS= 'SUCCESS' AND
tran_timestamp  >= TO_DATE('2019/07/01', 'yyyy/mm/dd')
AND tran_timestamp <= TO_DATE('2019/09/30','yyyy/mm/dd')
GROUP BY to_char(TRAN_TIMESTAMP, 'YYYY/MONTH/W')
ORDER BY to_char(TRAN_TIMESTAMP, 'YYYY/MONTH/W');

Monthly

SELECT to_char(tran_timestamp, 'YYYY/MM/MM') FROM TRANSACTIONS
WHERE
TRAN_STATUS = 'SUCCESS' AND
tran_timestamp  >= TO_DATE('2019/07/01', 'yyyy/mm/dd')
AND tran_timestamp <= TO_DATE('2019/09/30','yyyy/mm/dd')
GROUP BY to_char(TRAN_TIMESTAMP, 'YYYY/MONTH/MM')
ORDER BY to_char(TRAN_TIMESTAMP, 'YYYY/MONTH/MM');

For an example: Monthly result

Date     Total amt
2019/09  100
2019/07  500

But i need the result like :

Date     Total amt
2019/09  100
2019/08  0
2019/07  500

Answer

You need to generate all dates between your start and end date and join it with the result of your query.

Following query will give daily total amount. You need to change group by, order by and select clause if you want to get weekly and monthly result.

select all_dates.d as tran_timestamp, 
       sum(your_query.amt) as total_amt
 from
    (select date '2019-07-01' + level - 1 as d
       from dual
     connect by level <= (date '2019-09-30' - date '2019-07-01' ) + 1) all_dates 
Left join
    (SELECT trunc(tran_timestamp) as tran_timestamp , amt
       FROM TRANSACTIONS
      WHERE TRAN_STATUS = 'SUCCESS' 
        AND tran_timestamp  >= TO_DATE('2019/07/01', 'yyyy/mm/dd')
        AND tran_timestamp <= TO_DATE('2019/09/30','yyyy/mm/dd')) your_query
On (all_dates.d = tran_timestamp)
Group by all_dates.d
order by all_dates.d;

Cheers!!