shpresa
asked on
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.DashDietYe
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.DashDiet
any help???
ASKER
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.
So if SodiumDashDiet is not 2 , then set to zero
How can I do this?
Thanks a lot.
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.
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.
ASKER
select
s.ParticipantID,
s.DashDietYesNo,
s.SodiumDashDiet,
if SodiumDashDiet <> 2 then set SodiumDashDiet = 0
(count(isnull(s.DashDietYe sNo, '0')) + count(ISNULL(s.SodiumDashD iet, '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.DashDiet YesNo,s.So diumDashDi et
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
s.ParticipantID,
s.DashDietYesNo,
s.SodiumDashDiet,
if SodiumDashDiet <> 2 then set SodiumDashDiet = 0
(count(isnull(s.DashDietYe
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.DashDiet
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
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
...SUM(CASE when s.SodiumDashDiet = 2 then 1 else 0 end) AS CountOfParticipants...
Raj
from
ASKER
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.DashDiet YesNo,
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
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.DashDiet
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
great!, works fine.
Thanks a lot
Thanks a lot
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)
Open in new window