Oracle SQL – return the date record when there is no count result

I have the tables below and I need my query to bring me the amount of operations grouped by date. For the dates on which there will be no operations, I need to return the date anyway with the zero count. Kind like that:

OPERATION_DATE | COUNT_OPERATION | COUNT_OPERATION2 |
04/06/2019     |            453  |              81  |
05/06/2019     |              0  |               0  |

— QUERY I TRIED

SELECT 
    T1.DATE_OPERATION AS  DATE_OPERATION,
    NVL(T1.COUNT_OPERATION, '0') COUNT_OPERATION,
    NVL(T1.COUNT_OPERATION2, '0') COUNT_OPERATIONX,

FROM
(  
SELECT
    trunc(t.DATE_OPERATION) as DATE_OPERATION,
    count(t.ID_OPERATION) AS COUNT_OPERATION,
    COUNT(CASE WHEN O.OPERATION_TYPE = 'X' THEN 1 END) COUNT_OPERATIONX,
from OPERATION o
    left join OPERATION_TYPE ot on ot.id_operation = o.id_operation
where ot.OPERATION_TYPE in ('X', 'W', 'Z', 'I', 'J', 'V') 
    and TRUNC(t.DATE_OPERATION) >= to_date('01/06/2019', 'DD-MM-YYYY')
    group by trunc(t.DATE_OPERATION)
) T1

— TABLES

CREATE TABLE OPERATION
(   ID_OPERATION NUMBER NOT NULL,
    DATE_OPERATION DATE NOT NULL,
    VALUE NUMBER NOT NULL )

CREATE TABLE OPERATION_TYPE
(   ID_OPERATION NUMBER NOT NULL,
    OPERATION_TYPE VARCHAR2(1) NOT NULL,
    VALUE NUMBER NOT NULL)  

Answer

I guess that it is a calendar you need, i.e. a table which contains all dates involved. Otherwise, how can you display something that doesn’t exist?

This is what you currently have (I’m using only the operation table; add another one yourself):

SQL> with
  2  operation (id_operation, date_operation, value) as
  3    (select 1, date '2019-06-01', 100 from dual union all
  4     select 2, date '2019-06-01', 200 from dual union all
  5     -- 02/06/2019 is missing
  6     select 3, date '2019-06-03', 300 from dual union all
  7     select 4, date '2019-06-04', 400 from dual
  8    )
  9  select o.date_operation,
 10         count(o.id_operation)
 11  from operation o
 12  group by o.date_operation
 13  order by o.date_operation;

DATE_OPERA COUNT(O.ID_OPERATION)
---------- ---------------------
01/06/2019                     2
03/06/2019                     1
04/06/2019                     1

SQL>

As there are no rows that belong to 02/06/2019, query can’t return anything (you already know that).

---

Therefore, add a calendar. If you already have that table, fine – use it. If not, create one. It is a hierarchical query which adds level to a certain date. I’m using 01/06/2019 as the starting point, creating 5 days (note the connect by clause).

SQL> with
  2  operation (id_operation, date_operation, value) as
  3    (select 1, date '2019-06-01', 100 from dual union all
  4     select 2, date '2019-06-01', 200 from dual union all
  5     -- 02/06/2019 is missing
  6     select 3, date '2019-06-03', 300 from dual union all
  7     select 4, date '2019-06-04', 400 from dual
  8    ),
  9  dates (datum) as                              --> this is a calendar
 10    (select date '2019-06-01' + level - 1
 11     from dual
 12     connect by level <= 5
 13    )
 14  select d.datum,
 15         count(o.id_operation)
 16  from operation o full outer join dates d on d.datum = o.date_operation
 17  group by d.datum
 18  order by d.datum;

DATUM      COUNT(O.ID_OPERATION)
---------- ---------------------
01/06/2019                     2
02/06/2019                     0      --> missing in source table
03/06/2019                     1
04/06/2019                     1
05/06/2019                     0      --> missing in source table

SQL>

---

Probably a better option is to dynamically create a calendar so that it doesn’t depend on any hardcoded values, but uses the min(date_operation) to max(date_operation) time span. Here we go:

SQL> with
  2  operation (id_operation, date_operation, value) as
  3    (select 1, date '2019-06-01', 100 from dual union all
  4     select 2, date '2019-06-01', 200 from dual union all
  5     -- 02/06/2019 is missing
  6     select 3, date '2019-06-03', 300 from dual union all
  7     select 4, date '2019-06-04', 400 from dual
  8    ),
  9  dates (datum) as                              --> this is a calendar
 10    (select x.min_datum + level - 1
 11     from (select min(o.date_operation) min_datum,
 12                  max(o.date_operation) max_datum
 13           from operation o
 14          ) x
 15     connect by level <= x.max_datum - x.min_datum + 1
 16    )
 17  select d.datum,
 18         count(o.id_operation)
 19  from operation o full outer join dates d on d.datum = o.date_operation
 20  group by d.datum
 21  order by d.datum;

DATUM      COUNT(O.ID_OPERATION)
---------- ---------------------
01/06/2019                     2
02/06/2019                     0      --> missing in source table
03/06/2019                     1
04/06/2019                     1

SQL>