sql - How to include values that count nothing on certain day (APEX)

Question

I have this query:

SELECT  
COUNT(ID) AS FREQ,
TO_CHAR(TRUNC(CREATED_AT),'DD-MON') DATES
FROM TICKETS
WHERE TRUNC(CREATED_AT) > TRUNC(SYSDATE) - 32
GROUP BY TRUNC(CREATED_AT)
ORDER BY TRUNC(CREATED_AT) ASC

This counts how many tickets where created every day for the past month.
The result looks something like this:
(first 10 rows)

FREQ    DATES
3   28-DEC
4   04-JAN
8   05-JAN
1   06-JAN
4   07-JAN
5   08-JAN
2   11-JAN
6   12-JAN
3   13-JAN
8   14-JAN

The linechart that I created looks like this:

The problem is that the days where tickets are not created (in particular the weekends) the line just goes straight to the day where there is created a ticket.

Is there a way in APEX or in my query to include the days that aren't counted?

question from:https://stackoverflow.com/questions/65915199/how-to-include-values-that-count-nothing-on-certain-day-apex

Answer 1

I added a with clause to generate last 31 days, then I left joined with your base table like below.

with last_31_days as (
select trunc(sysdate) - 32 + level dt from dual connect by trunc(sysdate) - 32 + level < trunc(sysdate)
)
SELECT  
nvl(COUNT(t.ID), 0) AS FREQ,
TO_CHAR(
        nvl(TRUNC(t.CREATED_AT), a.dt)
    ,'DD-MON') DATES
FROM last_31_days a 
    LEFT JOIN TICKETS t 
        ON TRUNC(t.CREATED_AT) = a.dt 
GROUP BY nvl(TRUNC(t.CREATED_AT), a.dt)
ORDER BY 2 ASC
;