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

Question

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?

You can't select what's not there, so you'll need something like a calander table which has all the dates — HoneyBadger, Jan 19 '22 at 13:07
The problem are not the dates. I only want to count the dates that are registered in the table (skip the dates that are not in the table). If there is a document created on 12-10-22 with STATUS1 and for that date there is no document with STATUS2 , I want to obtain a row that count it as 0 — loliveira1999, Jan 19 '22 at 13:21
Does this answer your question? [Fill in date gaps for all groups](https://stackoverflow.com/questions/51947659/fill-in-date-gaps-for-all-groups) — astentx, Jan 19 '22 at 14:10

score 0 · Accepted Answer · answered Jan 19 '22 at 13:46

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>

score 0 · Answer 2 · answered Jan 19 '22 at 22:14

If you know the number of distinct statuses, but do not know dates, you may use partitioned outer join, which will generate all additional statuses for you:

create table t
as
select
  date '2022-01-01'
  + trunc(level/3) as dt
  , case
      when dbms_random.value() > 0.5
      then 'STATUS1'
      else 'STATUS2'
  end as status
from dual
connect by level < 15

with s(status) as (
  /*All possible statuses*/
  select 'STATUS1' from dual
  union all
  select 'STATUS2' from dual
)
, agg as (
  select
    dt, status
    , count(1) as cnt
  from t
  group by
    dt, status
)
select
  dt, status
  , nvl(cnt, 0) as cnt
from agg
  partition by (dt)
  right join s
    using (status)

DT        | STATUS  | CNT
:-------- | :------ | --:
01-JAN-22 | STATUS1 |   1
01-JAN-22 | STATUS2 |   1
02-JAN-22 | STATUS1 |   2
02-JAN-22 | STATUS2 |   1
03-JAN-22 | STATUS1 |   3
03-JAN-22 | STATUS2 |   0
04-JAN-22 | STATUS1 |   1
04-JAN-22 | STATUS2 |   2
05-JAN-22 | STATUS1 |   2
05-JAN-22 | STATUS2 |   1

db<>fiddle here

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

2 Answers2