sql - Include "0" counts in result set, DB2 -

i running query on db2 such:

select column_1, count(*) "my_count" "my_table" column_1 in(1, 2, 3, 4, 5, 6, 7, 8, 9, 10) , another_column = '20150609' group column_1; 

this works fine in terms of getting row counts grouped column_1. however, problem need result set contain "0" row in "in" clause has 0 count.

currently, results if 6, 7, , 8 don't have rows satisfy other "where" conditions:

column_1    my_count     1         33     2         20     3         14     4          2     5         33     9         27     10        16 

any idea how accomplish this? thanks!

here's simple solution using recursive cte generates values 1 10. once have series, left-join original query it, , you're set:

with v (column_1) (     select 1 sysibm.sysdummy1         union     select column_1 + 1 v column_1 <= 10 ) select v.column_1, count("my_table".column_1) "my_count" v left join "my_table" on v.column_1 = "my_table".column_1 -- note predicate must part of left join condition , another_column = '20150609' -- note predicate might no longer needed "my_table".column_1 in(1, 2, 3, 4, 5, 6, 7, 8, 9, 10) group v.column_1; 

from comments, take column_1 values aren't sequential. no problem, write equivalent query without recursive cte:

with v (column_1) (     select 1 sysibm.sysdummy1         union     select 42 sysibm.sysdummy1     union     select 1337 sysibm.sysdummy1 ) select v.column_1, count("my_table".column_1) "my_count" v left join "my_table" on v.column_1 = "my_table".column_1 , another_column = '20150609' group v.column_1;