Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SUM() from SELECT CASE? Expand / Collapse
How to get the SUM() from CASE?
Poll ResultsVotes
using SUM()
0%
0
Member Votes: 0, Anonymous Votes: 0. You don't have permission to vote within this poll.
Author
Message
Posted Saturday, November 15, 2008 8:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, December 27, 2008 4:50 PM
Points: 1, Visits: 15
Hi, I have below Select statement ... with 3 columns (DOC, PRODTYPE and TERM). In TERM field, there are 4 type of data (1,5,9,12). I need to display the COUNT() of 1 as 'YR' and COUNT() of 12 as 'MTH'. I also need another column with the total of these 2 counts ...How can I get the SUM() of Count of 'YR' and 'MTH'?



Select DOC, PRODTYPE, CASE TERM WHEN 1 THEN COUNT(CAST(TERM AS INT)) END as 'YR', CASE TERM WHEN 12 THEN COUNT(CAST(TERM AS INT)) END AS 'MTH'

GROUP BY DOC, PRODTYPE, TERM
Post #603226
Posted Saturday, November 15, 2008 11:28 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 22, 2012 4:46 PM
Points: 77, Visits: 110
There's likely a much simpler solution, but it's Saturday morning and I'm not quite "clicking" ;)

Try this:

with TermCTE (Doc, ProdType, Term, TermType) as
(Select
Doc,
ProdType,
Term,
TermType = case when Term = 1 then 'Yr'
when Term = 12 then 'Mth'
else 'Other'
end
from Table_name)

, sumCTE (Doc, ProdType, Term, YrSum, MthSum)
as
(Select
Doc,
ProdType,
Term,
YrSum = (Select sum(Term) from TermCTE where TermType = 'Yr'),
MthSum = (Select sum(Term) from TermCTE where TermType = 'Mth')
from TermCTE)

Select
Doc,
ProdType,
Term,
YrSum,
MthSum,
Sum_of_both = YrSum + MthSum
from sumCTE
Post #603245
Posted Saturday, November 15, 2008 11:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 10:58 AM
Points: 5,333, Visits: 25,272
Here is another way that might work for you.

CREATE TABLE #Table_1
(
[Doc] [varchar](50) NULL,
[Prodtype] [varchar](50) NULL,
[TERM] [int] NULL,
[Yr] [int] NULL,
[MTH] [int] Null
)
INSERT INTO #Table_1
Select DOC, PRODTYPE,TERM,
CASE TERM
WHEN 1 THEN COUNT(CAST(TERM AS INT))
ELSE 0
END as 'YR',
CASE TERM
WHEN 12 THEN COUNT(CAST(TERM AS INT))
ELSE 0
END AS 'MTH'
FROM TABLE_1
GROUP BY DOC, PRODTYPE,TERM

SELECT Doc, Prodtype, SUM(YR) AS 'YR', SUM(Mth) AS 'Mth', SUM(Yr + MTH) AS 'Gtotal'
FROM #Table_1
GROUP BY Doc, Prodtype

DROP TABLE #Table_1


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #603249
Posted Sunday, November 16, 2008 8:11 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
For another column, try this

SELECT DOC, PRODTYPE
,sum(CASE WHEN TERM = 1 THEN 1 ELSE 0 END) as 'YR'
,sum(CASE WHEN TERM = 12 THEN 1 ELSE 0 END) AS 'MTH'
,sum(CASE WHEN TERM IN (1,12) THEN 1 ELSE 0 END) as 'YRMTH'
From (table)
GROUP BY DOC, PRODTYPE

I'm not really sure why you're doing this with both case statements and with group by. Could you expose a small set of sample data and your desired results?


__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #603334
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse