Putting it all Together - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Putting it all Together

Author

Topic

JJ
Starting Member

23 Posts

Posted - 2002-05-28 : 11:02:29

Hi Guys,

I am trying to Count records in a stored procedure like this:

SELECT TotSIIPS = Count(SIIPResult) FROM SIIPEnclosure WHERE DatePart(month,DateEnrolled) = @Month AND DatePart(year ,DateEnrolled) = @Year

SELECT TotMisas = Count(SIIPResult) FROM SIIPEnclosure WHERE DatePart(month,DateEnrolled) = @Month AND DatePart(year ,DateEnrolled) = @Year AND SIIPResult = 1

SELECT TotDA = Count(SIIPResult) FROM SIIPEnclosure WHERE DatePart(month,DateEnrolled) = @Month AND DatePart(year ,DateEnrolled) = @Year AND SIIPResult = 2

SELECT TotMH = Count(SIIPResult) FROM SIIPEnclosure WHERE DatePart(month,DateEnrolled) = @Month AND DatePart(year ,DateEnrolled) = @Year AND SIIPResult = 3

SELECT TotNoneAbove = Count(SIIPResult) FROM SIIPEnclosure WHERE DatePart(month,DateEnrolled) = @Month AND DatePart(year ,DateEnrolled) = @Year AND SIIPResult = 4

When I execute this s.p. I only get the TotSIIPS value. How can I get all five results to show up like so :?

TotalSIIPS TotMisas TotDA TotMH TotNoneAbove

5 4 2 6 1

Thanks In Advance,

JJ

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-05-28 : 11:16:20

Not sure if it's the best way.. gotta be something that looks better

declare
@TotNoneAbove int,
@TotMH int,
@TotDA int,
@TotMisas int,
@TotSIIPS int

SELECT @TotSIIPS = Count(SIIPResult) FROM SIIPEnclosure WHERE DatePart(month,DateEnrolled) = @Month AND DatePart(year ,DateEnrolled) = @Year

SELECT @TotMisas = Count(SIIPResult) FROM SIIPEnclosure WHERE DatePart(month,DateEnrolled) = @Month AND DatePart(year ,DateEnrolled) = @Year AND SIIPResult = 1

SELECT @TotDA = Count(SIIPResult) FROM SIIPEnclosure WHERE DatePart(month,DateEnrolled) = @Month AND DatePart(year ,DateEnrolled) = @Year AND SIIPResult = 2

SELECT @TotMH = Count(SIIPResult) FROM SIIPEnclosure WHERE DatePart(month,DateEnrolled) = @Month AND DatePart(year ,DateEnrolled) = @Year AND SIIPResult = 3

SELECT @TotNoneAbove = Count(SIIPResult) FROM SIIPEnclosure WHERE DatePart(month,DateEnrolled) = @Month AND DatePart(year ,DateEnrolled) = @Year AND SIIPResult = 4

select @TotSIIPS as 'TotSIIPS', @TotMis as 'TotMis', @TotDA as 'TotDA', @TotMH as 'TotMH', @TotNoneAbove as 'TotNoneAbove'

Heh, butt ugly, but it'll work.

Had to edit.. couple copy/paste errors in a line... should work now

Edited by - M.E. on 05/28/2002 11:21:13

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-28 : 11:27:04


select
    count(*) as TotSIIPS,
    sum(case when isnull(SIIPResult,0) = 1 then 1 else 0 end) as TotMisas,
    sum(case when isnull(SIIPResult,0) = 2 then 1 else 0 end) as TotDa,
    sum(case when isnull(SIIPResult,0) = 3 then 1 else 0 end) as TotMH,
    sum(case when isnull(SIIPResult,0) = 4 then 1 else 0 end) as TotNoneAbove
from
    SIIPEnclosure
where
    datepart(mm,DateEnrolled) = @month and
    datepart(yy,DateEnrolled) = @year

<O>

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-05-28 : 11:29:47

Heh, I thought there was a case method for this