CODEWITHSUNDEEP
sql-servert-sql
junkone
junkone

Reputation: 1465

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<>closed) 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

Upvotes: 4

Views: 7102

Answers (3)

CarrieP05
CarrieP05

Reputation: 11

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.

Upvotes: 1

BonyT
BonyT

Reputation: 10940

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

Upvotes: 3

t-clausen.dk
t-clausen.dk

Reputation: 44316

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

Upvotes: 1

Related Questions