sql-server - 如何在相同的SQL查询中求和和小计

select problemrecordedbytitle,problemstatus , count(problemstatus) from problemtablewhere problemrecordedccyy >='2011'and problemrecordedbytitle like 'LPM%'group by problemrecordedbytitle, problemstatusorder by problemrecordedbytitle ascLPM-AMS-EDW Open 1LPM-AMS-EDW WIP 1LPM-AMS-EOM Closed 4这个SQL为我提供了很好的状态摘要。但是我今天有一个新的要求。我想总结所有的总和，即count（problemstatus）和count（problemstatus closed）。它应该像LPM-AMS-EDW NotClosed 2LPM-AMS-EDW Total 6我不确定如何在TSQL中做到这一点 (adsbygoogle = window.adsbygoogle || []).push({}); 最佳答案 您确实应该注意不要将具有不同值的字段分组。这将为您提供所描述的结果。;WITH a as(SELECT problemrecordedbytitle,problemstatus from problemtableWHERE problemrecordedccyy >='2011'and problemrecordedbytitle like 'LPM%')SELECT problemrecordedbytitle, 'NotClosed', COUNT(*) FROM a WHERE problemstatus <> 'Closed'GROUP BY problemrecordedbytitleUNION ALLSELECT MIN(problemrecordedbytitle), 'Total', COUNT(*) FROM a我将其重写为防止这些组问题：declare @problemtable table (problemrecordedbytitle varchar(20), problemstatus varchar(10))INSERT @problemtable values('LPM-AMS-EDW', 'Open')INSERT @problemtable values('LPM-AMS-EDW','WIP')INSERT @problemtable values('LPM-AMS-EOM','Closed')INSERT @problemtable values('LPM-AMS-EOM','Closed')INSERT @problemtable values('LPM-AMS-EOM','Closed')INSERT @problemtable values('LPM-AMS-EOM','Closed');WITH a as(SELECT left(problemrecordedbytitle, 3) problemrecordedbytitle,problemstatusFROM @problemtable -- replace this tablename for your script-- You need these lines for your script-- WHERE problemrecordedccyy >='2011'-- and problemrecordedbytitle like 'LPM%')SELECT problemrecordedbytitle, 'NotClosed' [status], COUNT(*) count FROM a WHERE problemstatus <> 'Closed'GROUP BY problemrecordedbytitleUNION ALLSELECT MIN(problemrecordedbytitle), 'Total', COUNT(*) FROM a结果：problemrecordedbytitle status count---------------------- --------- -----------LPM NotClosed 2LPM Total 6 (adsbygoogle = window.adsbygoogle || []).push({});