Блог

 Select b.PTW_MRC,DBO.r5o7_o7get_desc('EN','MRC', b.PTW_MRC,NULL, null) as Department,count(*) as CountofDept from U5PERMITAUDIT a inner join R5PERMITTOWORK b on a.PTW_CODE = b.PTW_CODE Where CREATED between '2016-01-01' and '2021-11-10' and PTW_MRC = PTW_MRC--isnull(@Dept,PTW_MRC)  and PTW_RESP = PTW_RESP-- isnull(@Auditedby,PTW_RESP) group by PTW_MRC,DBO.r5o7_o7get_desc('EN','MRC', PTW_MRC,NULL, null)  UNION ALL  Select b.PTW_MRC, DBO.r5o7_o7get_desc('EN','MRC', b.PTW_MRC,NULL, null) as Department,count(*) as CountofDept from U5PERMITAUDIT a inner join R5PERMITTOWORK b on a.PTW_CODE = b.PTW_CODE Where CREATED between '2016-01-01' and '2021-11-10' and PTW_MRC = PTW_MRC--isnull(@Dept,PTW_MRC)  and PTW_RESP2 = PTW_RESP2--isnull(@Auditedby,PTW_RESP2) group by PTW_MRC,DBO.r5o7_o7get_desc('EN','MRC', PTW_MRC,NULL, null)  

Мой текущий результат

Я хочу объединить количество отделов, чьи отделы одинаковы.. Пожалуйста, Помогите!!!

Просто завершите свой запрос и выполните другую группировку и подведите по нему итоги.

 WITH cte_union AS (  Select b.PTW_MRC,DBO.r5o7_o7get_desc('EN','MRC', b.PTW_MRC,NULL, null) as Department,count(*) as CountofDept  from U5PERMITAUDIT a inner join R5PERMITTOWORK b on a.PTW_CODE = b.PTW_CODE  Where CREATED between '2016-01-01' and '2021-11-10'  and PTW_MRC = PTW_MRC--isnull(@Dept,PTW_MRC)   and PTW_RESP = PTW_RESP-- isnull(@Auditedby,PTW_RESP)  group by PTW_MRC,DBO.r5o7_o7get_desc('EN','MRC', PTW_MRC,NULL, null)   UNION ALL   Select b.PTW_MRC, DBO.r5o7_o7get_desc('EN','MRC', b.PTW_MRC,NULL, null) as Department,count(*) as CountofDept  from U5PERMITAUDIT a inner join R5PERMITTOWORK b on a.PTW_CODE = b.PTW_CODE  Where CREATED between '2016-01-01' and '2021-11-10'  and PTW_MRC = PTW_MRC--isnull(@Dept,PTW_MRC)   and PTW_RESP2 = PTW_RESP2--isnull(@Auditedby,PTW_RESP2)  group by PTW_MRC,DBO.r5o7_o7get_desc('EN','MRC', PTW_MRC,NULL, null) ) SELECT PTW_MRC, Department, SUM(CountofDept) FROM cte_union GROUP by PTW_MRC, Department;