include 0 in count sql -

include 0 in count sql -

suppose have table:

|my_table| | id_col | | 1 | | 1 | | 2 | | 2 |

now want count query on table include 0 results. when seek run query:

select id_col,count(id_col) my_table id_col = '1' or id_col = '2' or id_col = '100' grouping id_col

the result 2 rows. want display 0 id_col = '100'. there way this?

thanks.

btw, using db2.

you have create table on fly contain record per id:

select 1 id_col union select 2 union select 100;

then can select ids table , show count:

select all_ids.id_col, count(my_table.id_col) (select 1 id_col union select 2 union select 3) all_ids left bring together my_table on my_table.id_col = all_ids.id_col grouping all_ids.id_col;

edit: tagged request db2: db2 needs dummy table select 1 value from. rather select 1 1 must write select 1 sysibm.sysdummy1. finish query:

select all_ids.id_col, count(my_table.id_col) ( select 1 id_col sysibm.sysdummy1 union select 2 sysibm.sysdummy1 union select 3 sysibm.sysdummy1 ) all_ids left bring together my_table on my_table.id_col = all_ids.id_col grouping all_ids.id_col;

sql count db2