Get 0 if value dosen´t exist on table - oracle

I have a query where it return diferent info about sales by hour, the problem is that I need to get 0 if at cerrtain hour the whas no registry on that table.

Here is the query:

SELECT COUNT(a.DOC_NO) AS "# TICKET",
       SUM(a.TRANSACTION_TOTAL_AMT) AS "$ TICKET",
       SUM(a.sold_qty) AS "# ITEMS",  a.store_code AS "TIENDA",
       to_char(a.CREATED_DATETIME,'yyyy-mm-dd') AS "FECHA",
       to_char(a.CREATED_DATETIME,'HH24') || ':00' AS "HORA"  
FROM document a  
WHERE a.receipt_type in (0,1) 
AND a.status=4 AND a.store_name!='CEDIS' 
AND to_char(a.CREATED_DATETIME,'yyyy-mm-dd') = '2024-06-01' 
AND a.STORE_CODE = 'ESF' 
GROUP BY a.store_code,to_char(a.CREATED_DATETIME,'yyyy-mm-dd'), to_char(a.CREATED_DATETIME,'HH24') 
ORDER BY a.store_code, to_char(a.CREATED_DATETIME,'yyyy-mm-dd'), to_char(a.CREATED_DATETIME,'HH24')

So I get something like this:

TICKETS	AMOUNT	ITEMS	STORE	DATE	HOUR
2	1398	2	ESF	2024-06-01	12:00
1	819	2	ESF	2024-06-01	14:00
2	758.6	4	ESF	2024-06-01	15:00
4	1386.6	4	ESF	2024-06-01	16:00

So far, I tried this:

SELECT CASE WHEN EXISTS(
        SELECT  COUNT(a.DOC_NO) AS "# TICKET",
    SUM(a.TRANSACTION_TOTAL_AMT) AS "$ TICKET",
        SUM(a.sold_qty) AS "# ITEMS", a.store_code AS "TIENDA",
        to_char(a.CREATED_DATETIME,'yyyy-mm-dd') AS "FECHA",
        to_char(a.CREATED_DATETIME,'HH24') || ':00' AS "HORA"
    FROM document a
    WHERE a.receipt_type in (0,1) and a.status=4 
        AND a.store_name!='CEDIS' 
        AND to_char(a.CREATED_DATETIME,'yyyy-mm-dd') = '2024-06-01' 
        AND a.STORE_CODE = 'ESF'
GROUP BY a.store_code,to_char(a.CREATED_DATETIME,'yyyy-mm-dd'), to_char(a.CREATED_DATETIME,'HH24')
ORDER BY a.store_code, to_char(a.CREATED_DATETIME,'yyyy-mm-dd'), to_char(a.CREATED_DATETIME,'HH24')
)
THEN to_char(a.CREATED_DATETIME,'HH24')
ELSE NULL END AS hours
FROM (VALUES('12'),('13'),('14'),('15'),('16')) CON(hours)

But I think I´m missing something, found this way on another post but it all I get is "missing right parentesis".

So, what I need to get is that, if at 13:00 there was no sale the query shows 0 (or null)

TICKETS	AMOUNT	ITEMS	STORE	DATE	HOUR
2	1398	2	ESF	2024-06-01	12:00
0	0	0	ESF	2024-06-01	13:00
1	819	2	ESF	2024-06-01	14:00
2	758.6	4	ESF	2024-06-01	15:00
4	1386.6	4	ESF	2024-06-01	16:00

Solution

Use a row-generator to generate all the hours and then use PARTITIONed OUTER JOIN and then wrap the SUM aggregations in COALESCE to change the NULL values to 0:

SELECT COUNT(a.DOC_NO) AS "# TICKET",
       COALESCE(SUM(a.TRANSACTION_TOTAL_AMT), 0) AS "$ TICKET",
       COALESCE(SUM(a.sold_qty), 0) AS "# ITEMS",
       a.store_code AS "TIENDA",
       a.day AS "FECHA",
       h.hour AS "HORA"  
FROM   ( SELECT TO_CHAR(LEVEL - 1, 'FM00') || ':00' AS hour
         FROM   DUAL
         CONNECT BY LEVEL <= 24 ) h
       LEFT OUTER JOIN (
         SELECT store_code,
                doc_no,
                transaction_total_amt,
                sold_qty,
                TO_CHAR(CREATED_DATETIME,'yyyy-mm-dd') AS day,
                TO_CHAR(CREATED_DATETIME,'HH24') || ':00' AS hour
         FROM   document
         WHERE  receipt_type in (0,1) 
         AND    status=4
         AND    store_name!='CEDIS' 
         AND    CREATED_DATETIME >= DATE '2024-06-01' 
         AND    CREATED_DATETIME <  DATE '2024-06-02'
         AND    STORE_CODE = 'ESF' 
       ) a
       PARTITION BY (a.store_code, a.day)
       ON (a.hour = h.hour)
GROUP BY
       a.store_code,
       a.day,
       h.hour 
ORDER BY
       a.store_code,
       a.day,
       h.hour

Which, for the sample data:

CREATE TABLE document (
  doc_no, transaction_total_amt, sold_qty, created_datetime,
  receipt_type, status, store_name, store_code
) AS
SELECT 1,
       10,
       100,
       DATE '2024-06-01' + INTERVAL '0' HOUR,
       0,
       4,
       'NOT_CEDIS',
       'ESF'
FROM   DUAL;

Outputs:

# TICKET	$ TICKET	# ITEMS	TIENDA	FECHA	HORA
1	10	100	ESF	2024-06-01	00:00
0	0	0	ESF	2024-06-01	01:00
0	0	0	ESF	2024-06-01	02:00
0	0	0	ESF	2024-06-01	03:00
0	0	0	ESF	2024-06-01	04:00
0	0	0	ESF	2024-06-01	05:00
0	0	0	ESF	2024-06-01	06:00
0	0	0	ESF	2024-06-01	07:00
0	0	0	ESF	2024-06-01	08:00
0	0	0	ESF	2024-06-01	09:00
0	0	0	ESF	2024-06-01	10:00
0	0	0	ESF	2024-06-01	11:00
0	0	0	ESF	2024-06-01	12:00
0	0	0	ESF	2024-06-01	13:00
0	0	0	ESF	2024-06-01	14:00
0	0	0	ESF	2024-06-01	15:00
0	0	0	ESF	2024-06-01	16:00
0	0	0	ESF	2024-06-01	17:00
0	0	0	ESF	2024-06-01	18:00
0	0	0	ESF	2024-06-01	19:00
0	0	0	ESF	2024-06-01	20:00
0	0	0	ESF	2024-06-01	21:00
0	0	0	ESF	2024-06-01	22:00
0	0	0	ESF	2024-06-01	23:00

fiddle