• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 377
  • Last Modified:

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???
0
shpresa
Asked:
shpresa
1 Solution
 
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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
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
 
shpresaAuthor Commented:
great!, works fine.
Thanks a lot
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now