SUM() from SELECT CASE?

Question

Post reply

SUM() from SELECT CASE?

yeeyeeliew

SSC Veteran

Points: 223
More actions
November 15, 2008 at 8:40 am

#195949

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

epriddy Old Hand Points: 343 More actions · Answer 1

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

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[/url]
Before posting a performance problem please read[/url]

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

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? Everybody look what's going down. -- Stephen Stills