sql - group by - counting rows and if not existing add row with 0 value -
i know asked similar question, here start zero... without giving query tried don't influance you.
if table:
status pgid nvarchar5 nvarchar10 catid tp_id isactive null information technology null 1 1 1 hr null human recource null 1 2 1 fin null finance null 1 3 1 new 1 null 1354 2 10001 1 new 1 null 464 2 10002 1 new 1 null 13465 2 10003 1 active 1 null 79846 2 10004 1 deleted 1 null 132465 2 10005 1 new 2 null 79847 2 10006 1 new 2 null 341 2 10007 1 deleted 2 null 465 2 10008 1 deleted 2 null 132 2 10009 1 deleted 2 null 465 2 10010 1 deleted 2 null 1 2 10011 1 new 3 null 465 2 10012 1 new 3 null 1465 2 10013 1 new 3 null 132 2 10014 1 null null null null 3 20136 1 null null null null 4 22165 1 null null null null 3 24566 1 null null null null 10 24566 1 what should query if want result this:
status pgid nvarchar5 total new 1 information technology 3 active 1 information technology 1 deleted 1 information technology 1 new 2 human recource 2 active 2 human recource 0 deleted 2 human recource 4 new 3 finance 3 active 3 finance 0 deleted 3 finance 0 or not possible?
edit: if want see i've tried: wrong number in count()
update: how calculate total: 
i won't bother going how bad data looks, column names etc , presume it's case of 'this have work with'. given data try
select t.status, t.department_id, t.department, coalesce(s.total, 0) total ( select nvarchar5 department, tp_id department_id, status my_table, (select 'new' status union select 'active' status union select 'deleted' status ) m tp_id in (1,2,3) ) t left join ( select status status, pgid department_id, count(1) total my_table pgid not null group status, pgid ) s on t.status = s.status , t.department_id = s.department_id
Comments
Post a Comment