November 15, 2008 at 8:40 am
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
November 15, 2008 at 11:28 am
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
November 15, 2008 at 11:40 am
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
November 16, 2008 at 8:11 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply