how to sum and subtotal in same tsql query

select problemrecordedbytitle,problemstatus ,
       count(problemstatus) from problemtable 
where problemrecordedccyy >='2011'
and problemrecordedbytitle like 'LPM%'
group by problemrecordedbytitle, problemstatus
order by problemrecordedbytitle asc

LPM-AMS-EDW Open    1
LPM-AMS-EDW WIP     1
LPM-AMS-EOM Closed  4

this sql provides me with nice summary of statuses. however i got a new requirement today.

i want to sum of all ie count(problemstatus) and count(problemstatus<>cl开发者_如何转开发osed) in summary. it should be like

LPM-AMS-EDW    NotClosed    2
LPM-AMS-EDW    Total        6

I am not sure how i can do it in TSQL

Check out Rollup:

http://msdn.microsoft.com/en-us/library/ms177673.aspx

or for 2005: http://msdn.microsoft.com/en-us/library/ms177673%28v=SQL.90%29.aspx

 select problemrecordedbytitle, problemstatus, count(problemstatus) from problemtable 
 where problemrecordedccyy >='2011'
 and problemrecordedbytitle like 'LPM%'
 group by rollup(problemrecordedbytitle, problemstatus)
 order by problemrecordedbytitle asc

If you want to group different statuses then something like:

 select problemrecordedbytitle, 
 case problemstatus when 4 then 'Closed' ELSE 'NotClosed' END as Status,
 Count(1)
 where problemrecordedccyy >='2011'
 and problemrecordedbytitle like 'LPM%'
 group by rollup(problemrecordedbytitle, case problemstatus when 4 then 'Closed' ELSE 'NotClosed' END)

For 2005 I think the syntax is something like

 .....
 GROUP BY problemrecordedbytitle, case problemstatus when 4 then 'Closed' ELSE 'NotClosed' END
 WITH ROLLUP

May not be exactly as above, but you can definitely do this with 2005

You really should be careful not to group on fields with different values. This will give you the result you are looking for as described.

;WITH a as(
SELECT problemrecordedbytitle,problemstatus from problemtable  
WHERE problemrecordedccyy >='2011' 
and problemrecordedbytitle like 'LPM%' 
)
SELECT problemrecordedbytitle, 'NotClosed', COUNT(*) FROM a WHERE problemstatus <> 'Closed' 
GROUP BY problemrecordedbytitle
UNION ALL
SELECT MIN(problemrecordedbytitle), 'Total', COUNT(*) FROM a

I would rewrite it to this to prevent those group problems:

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,problemstatus 
FROM @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 problemrecordedbytitle
UNION ALL
SELECT MIN(problemrecordedbytitle), 'Total', COUNT(*) FROM a

Result:

problemrecordedbytitle status    count
---------------------- --------- -----------
LPM                    NotClosed 2
LPM                    Total     6

select COUNT(problemrecordedbytitle) AS Problem_Rec_Title, MAX(problemrecordedbytitle) as Prob_Rec_Title, problemstatus
       where problemrecordedccyy >='2011'
and problemrecordedbytitle like 'LPM%'
group by problemrecordedbytitle, problemstatus
order by problemrecordedbytitle asc

Play around with COUNT and MAX - This is how I solved my issue which was similar.

COUNT counts, then MAX gives you the max number (a sub-total of sorts). Then you can group and order by the fields.

I hope this helps.