How to get rows with COUNT = 0 in an Oracle SQL Query?-CodePudding

I have a Oracle SQL table which stores Document Informations. The table has 3 columns (ID, creationDate, status). There are only 2 status ("STATUS1", "STATUS2")

For each available date and status, I want to have the total number of documents with those characteristics (including when total count = 0).

My code is the following:

SELECT DISTINCT TO_CHAR(D.CREATED_ON, 'DD-MM-YY') AS DATE, D.STATUS, COUNT(*) AS TOTAL
FROM DOCUMENTS D
GROUP BY DISTINCT TO_CHAR(D.CREATED_ON, 'DD-MM-YY'), D.STATUS
ORDER BY 1 ASC;

Which returns:

| DATE      | STATUS | TOTAL |
|-----------|--------|-------|
| 14-01-22  | STATUS1| 2     |
| 14-01-22  | STATUS2| 1     |
| 15-01-22  | STATUS2| 3     |
| 16-01-22  | STATUS1| 2     |

I want it to return:

| DATE     | STATUS | TOTAL |
|----------|--------|-------|
| 14-01-22 |STATUS1 | 2     |
| 14-01-22 |STATUS2 | 1     |
| 15-01-22 |STATUS1 | 0     | <--
| 15-01-22 |STATUS2 | 3     |
| 16-01-22 |STATUS1 | 2     |
| 16-01-22 |STATUS2 | 0     | <--

Is this possible?

CodePudding user response：

You'll need some help - as HoneyBadger commented, a kind of calendar. Two of them, actually, to name all statuses and all dates involved.

With sample data

SQL> with documents (id, created_on, status) as
  2    (select 1, date '2022-01-14', 'STATUS1' from dual union all
  3     select 2, date '2022-01-14', 'STATUS1' from dual union all
  4     select 3, date '2022-01-14', 'STATUS2' from dual union all
  5     select 4, date '2022-01-15', 'STATUS2' from dual union all
  6     select 5, date '2022-01-15', 'STATUS2' from dual union all
  7     select 6, date '2022-01-15', 'STATUS2' from dual union all
  8     select 7, date '2022-01-16', 'STATUS1' from dual union all
  9     select 8, date '2022-01-16', 'STATUS1' from dual
 10    ),

and the help,

 11  all_statuses as
 12    (select distinct status
 13     from documents
 14    ),
 15  all_dates as
 16    (select distinct created_on
 17     from documents
 18    )
 19  select s.created_on, a.status, count(d.id) total
 20  from all_statuses a cross join all_dates s
 21       left join documents d on d.status = a.status and d.created_on = s.created_on
 22  group by s.created_on, a.status
 23  order by s.created_on, a.status;

the result is

CREATED_ON STATUS       TOTAL
---------- ------- ----------
14-01-2022 STATUS1          2
14-01-2022 STATUS2          1
15-01-2022 STATUS1          0
15-01-2022 STATUS2          3
16-01-2022 STATUS1          2
16-01-2022 STATUS2          0

6 rows selected.

SQL>