mysql - Add new rows with count = 0 present in others groupby group -

i have table:

||id || group || status    || 1 ||  aaaa ||  aaaaa   || 2 ||  aaaa ||  bbbbb   || 3 ||  bbbb || aaaaa    || 4 ||  bbbb ||  bbbbb   || 5 ||  bbbb ||  aaaaa     || 6 ||  bbbb ||  ccccc    || 7 ||  bbbb ||  ddddd  

i want result of query:

|| status || group || count(status)   || aaaaa || aaaa  || 1   || bbbbb || aaaa  || 1   || ccccc || aaaa  || 0    || ddddd || aaaa  || 0  || aaaaa || bbbb  || 2   || bbbbb || bbbb  || 1   || ccccc || bbbb  || 1   || ddddd || bbbb  || 1   

i tried:

select status, count(status), group       table       group status, group   

but code can't add lines count = 0

first, want generate combinations of status , group. can using cross join. then, left join on table count

sql fiddle

with cte as(     select         a.[group], b.[status]     (         select distinct [group] yourtable     )a     cross join (         select distinct [status] yourtable     )b ) select     c.[status],     c.[group],     isnull(count(t.id), 0) statuscount cte c left join yourtable t     on t.[status] = c.status     , t.[group] = c.[group] group c.[status], c.[group] 

---

using subquery:

sql fiddle

select     c.[status],     c.[group],     isnull(count(t.id), 0) statuscount (     select * from(         select             a.[group], b.[status]         (             select distinct [group] yourtable         )a         cross join (             select distinct [status] yourtable         )b     )t ) c left join yourtable t     on t.[status] = c.status     , t.[group] = c.[group] group c.[status], c.[group]