include 0 in count sql

Question

Suppose I have this table:

|MY_TABLE|
| ID_COL |
|   1    |
|   1    |
|   2    |
|   2    |

Now I want to do a count query on this table that would include zero results. When I try to run my query:

Select ID_COL,count(ID_COL) 
from   MY_TABLE 
where ID_COL = '1' or ID_COL = '2' or ID_COL = '100' group by ID_COL

The result will only be two rows. I want to display 0 for ID_COL = '100'. Is there a way to do this?

Thanks.

BTW, I am using DB2.

You should always add a tag to name your dbms (MySQL or Oracle or SQL Server or whatever). — Thorsten Kettner, Nov 13 '14 at 09:49
Note that the COUNT() for ID_COL=100 is not zero; rather it doesn't exist. — user2338816, Nov 15 '14 at 09:09

Thorsten Kettner · Accepted Answer · 2014-11-13T13:48:49.530

You would have to create a table on the fly to contain a record per ID:

select 1 as id_col union all select 2 union all select 100;

Then you can select IDs from this table and show the count:

select all_ids.id_col, count(my_table.id_col)
from (select 1 as id_col union all select 2 union all select 3) all_ids
left join my_table on my_table.id_col = all_ids.id_col
group by all_ids.id_col;

EDIT: Now that you tagged your request with DB2: DB2 needs a dummy table to select one value from. Rather then select 1 one must write select 1 from sysibm.sysdummy1. The complete query:

select all_ids.id_col, count(my_table.id_col)
from 
(
  select 1 as id_col from sysibm.sysdummy1
  union all 
  select 2 from sysibm.sysdummy1
  union all 
  select 3 from sysibm.sysdummy1
) all_ids
left join my_table on my_table.id_col = all_ids.id_col
group by all_ids.id_col;

if 3 is not found your query will still return 1 cause it exist in all_ids — ah_hau, Nov 13 '14 at 09:58
@ah_hau: You are right. I'll edit. Instead of COUNT(\*), I must use COUNT(my_table.id_col) to exclude non-matches from the counting. Thanks. — Thorsten Kettner, Nov 13 '14 at 10:06

include 0 in count sql

1 Answers1