mysql - Group by - return counts that are zero -

this question has answer here:

• return count 0 mysql group by 4 answers

i have query looks this:

select rank, count(distinct member_id) mcount my_table group rank order field(rank, 1,2,3,4,5,6,7,8,9,10,0); 

it gives me list, , count result has 0, rank may not show, example:

rank | mcount 1    | 2 3    | 2 4    | 2 5    | 2 6    | 2 7    | 2 8    | 2 9    | 2 10   | 2 

as can see ranks 2 , 0 don't show, them show count of 0 this:

rank | mcount 1    | 2 2    | 0 3    | 2 4    | 2 5    | 2 6    | 2 7    | 2 8    | 2 9    | 2 10   | 2 0    | 0 

what can accomplish this?

you need table values 0 thru 10 in. since including zero, can't use auto_increment table, these tables start one, default.

you create table contains these values. example, following create table values 0 thru 10:

create table if not exists ids (     tinyint unsigned auto_increment not null primary key ) engine=innodb;  set sql_mode='no_auto_value_on_zero';  insert ids select 0 union select null; -- insert 0 , 1  insert ids select null  ids ,ids b ,ids c ,ids d limit 9; -- insert 2 thru 10 

or use:

   select a.i * 4 + b.i      (select a.i * 2 + b.i (select 0 union select 1) a, (select 0 union select 1) b) a,      (select a.i * 2 + b.i (select 0 union select 1) a, (select 0 union select 1) b) b    order 1    limit 11 

which return numbers 0 thru 10.

using ids table, query be:

select ids.i rank, ifnull(count(distinct my_table.member_id), 0) mcount ids inner join my_table on my_table.rank = ids.i group ids.i order field(ids.i, 1,2,3,4,5,6,7,8,9,10,0); 

or using select query, query be:

select ids.i rank, ifnull(count(distinct my_table.member_id), 0) mcount  ( select a.i * 4 + b.i   (select a.i * 2 + b.i (select 0 union select 1) a, (select 0 union select 1) b) a,   (select a.i * 2 + b.i (select 0 union select 1) a, (select 0 union select 1) b) b order 1 limit 11 ) ids inner join my_table on my_table.rank = ids.i group ids.i order field(ids.i, 1,2,3,4,5,6,7,8,9,10,0);