count / sum /cases in MS SQL




I need to count as 1 the participant that have a value s.SodiumDashDiet = 2 in the database. The rest of the values I need to show as zero., basically set them zero..this is what i have so far

select
s.ParticipantID,
s.DashDietYesNo,
s.SodiumDashDiet ,
(count(isnull(s.DashDietYesNo, '0'))  + count(ISNULL(s.SodiumDashDiet, '0'))) as sum2
from tbl_survey s
inner join #tbl_TempAdultAttendance p on s.ParticipantID = p.ParticipantID
inner join tbl_collectionpoint cl on s.collectionpointid = cl.collectionpointid  
where cl.collectionid = 2 and p.result = 5 and s.SodiumDashDiet = 2 and s.DashDietYesNo = 1
group by s.ParticipantID,s.DashDietYesNo,s.SodiumDashDiet

any help???
shpresaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris MConsulting - Technology ServicesCommented:
Showing and counting are two different things.
you can count ten zeroes and sum them up as zero.

To count the participants that have a value s.SodiumDashDiet = 2, I think the script u want looks like my snippet (attached)

select
count(*) As 'Participants with SodiumDashDiet of 2',
s.ParticipantID,
s.DashDietYesNo,
s.SodiumDashDiet,
(count(isnull(s.DashDietYesNo, '0'))  + count(ISNULL(s.SodiumDashDiet, '0'))) as sum2
from tbl_survey s
inner join #tbl_TempAdultAttendance p on s.ParticipantID = p.ParticipantID
inner join tbl_collectionpoint cl on s.collectionpointid = cl.collectionpointid  
where cl.collectionid = 2 and p.result = 5 and s.SodiumDashDiet = 2 and s.DashDietYesNo = 1
group by s.ParticipantID,s.DashDietYesNo,s.SodiumDashDiet

Open in new window

0
shpresaAuthor Commented:
I tryed that and it gives me no data while there is data. The thing is that If no data found I need it to be set to 2.
So if  SodiumDashDiet is not 2 , then set to zero

How can I do this?

Thanks a lot.
0
metechguruCommented:
I need to count as 1 the participant that have a value s.SodiumDashDiet = 2 in the database. The rest of the values I need to show as zero., basically set them zero..this is what i have so far  ---


Sorry I didn't go through your complete query . After reading your first line it looks like you can use case.

select
case when s.SodiumDashDiet = 2 then participant =1 else 0 end
from
A.
In this if SodiumDashDiet=2 in the database then the participant will come out as 1 else 0.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

shpresaAuthor Commented:
select
s.ParticipantID,
s.DashDietYesNo,
s.SodiumDashDiet,
if SodiumDashDiet <> 2 then set SodiumDashDiet = 0
(count(isnull(s.DashDietYesNo, '0'))  + count(ISNULL(s.SodiumDashDiet, '0'))) as sum2
from tbl_survey s
inner join #tbl_TempAdultAttendance p on s.ParticipantID = p.ParticipantID
inner join tbl_collectionpoint cl on s.collectionpointid = cl.collectionpointid  
where cl.collectionid = 2 and p.result = 5 and s.SodiumDashDiet = 2 --and s.DashDietYesNo = 1
group by s.ParticipantID,s.DashDietYesNo,s.SodiumDashDiet




so basically if SodiumDashDiet <> 2 then set SodiumDashDiet = 0
after i set it to zero...i need to count it. I have different values of SodiumDashDiet and all i care is the value of 2. I need to know only how many participant have choosen 2.
The query you gave me is correct but because it doesent found a 2 , it wont execute the other values it wont do the count .(it wont give me any result) I need .to count as 1 the values  SodiumDashDiet = 2 and count as 0 SodiumDashDiet = any value but 2.

Thanks again
0
Rajkumar GsSoftware EngineerCommented:
Try this in SELECT query to get the count of participants with SodiumDashDiet = 2

...SUM(CASE when s.SodiumDashDiet = 2 then 1 else 0 end) AS CountOfParticipants...

Raj
from
0
shpresaAuthor Commented:
i have this so far

select
s.ParticipantID,
s.DashDietYesNo,
s.SodiumDashDiet ,
sum(case when  s.DashDietYesNo = 1 then 1 else 0 end) as ccount1,
sum(case when s.SodiumDashDiet = 2 then 1 else 0 end) as ccount2,
sum (s.DashDietYesNo  +  s.SodiumDashDiet) as sum1

from tbl_survey s
inner join #tbl_TempAdultAttendance p on s.ParticipantID = p.ParticipantID
inner join tbl_collectionpoint cl on s.collectionpointid = cl.collectionpointid where cl.collectionid = 2 and p.result = 5  
group by s.Participantid,s.DashDietYesNo,
s.SodiumDashDiet

instead of summing sum (s.DashDietYesNo  +  s.SodiumDashDiet) as sum1
i need to sume the ccoun1 + ccount2  but when i put them, it is not recognizing as cummon.

this is the result i get from the query.
ParticipantID DashDietYEsNo SoudiumDAshDiet ccount1 ccount2 sum1
--------------------------------------------------------------------------------------------
804      0      0      0      0      0
806      1      92      1      0      93
807      0      0      0      0      0
809      0      3      0      0      3

I need to sum ccount 1 + ccount2 instead.
any help?

Thanks a lot

0
metechguruCommented:
select
s.ParticipantID,
s.DashDietYesNo,
s.SodiumDashDiet ,
sum ((case when  s.DashDietYesNo = 1 then 1 else 0 end) + (case when s.SodiumDashDiet = 2 then 1 else 0 end)  )
from tbl_survey s
inner join #tbl_TempAdultAttendance p on s.ParticipantID = p.ParticipantID
inner join tbl_collectionpoint cl on s.collectionpointid = cl.collectionpointid where cl.collectionid = 2 and p.result = 5  
group by s.Participantid,s.DashDietYesNo,
s.SodiumDashDiet

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shpresaAuthor Commented:
great!, works fine.
Thanks a lot
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.