Sql - Count value in case statement

Question

Post reply

Sql - Count value in case statement

m.mukesh.mca2009

SSC Enthusiast

Points: 154
More actions
August 5, 2014 at 6:24 am

#291680

Hi
My table structure like below.
id date value1 value2
1 5-june-2104 Yes No
1 6-june-2014 No Yes
2 5-june-2104 Yes Yes
Want to calculate yes count on any day for same id
Thanks in advance

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Maybe something like this?

WITH SampleData(id, date, value1, value2) AS(

SELECT 1, '21040605', 'Yes', 'No' UNION ALL

SELECT 1, '21040606', 'No', 'Yes' UNION ALL

SELECT 2, '21040605', 'Yes', 'Yes'

)

SELECT id,

COUNT(CASE WHEN value1 = 'Yes' THEN 1 END) value1YesCount,

COUNT(CASE WHEN value2 = 'Yes' THEN 1 END) value2YesCount

FROM SampleData

GROUP BY id

When values are not equal to 'Yes', the value returned is null and eliminated by the aggregate function (COUNT in this case).

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

m.mukesh.mca2009 SSC Enthusiast Points: 154 More actions · Answer 2

Thanks Luis. But each id has 1000 records with different date. How can i calculate count for range of date.

Sindhu Ravi SSC-Addicted Points: 455 More actions · Answer 3

A sample table looks like below

iddate value1value2

12104-06-05 00:00:00.000YesNo

12104-06-06 00:00:00.000NoYes

12104-06-05 00:00:00.000YesNo

12104-06-06 00:00:00.000NoYes

22104-06-05 00:00:00.000YesYes

22104-06-05 00:00:00.000NoYes

22104-06-05 00:00:00.000YesYes

22104-06-05 00:00:00.000NoYes

Query to calculate 'YES' count of both value1 and value2 for range of date

SELECT id,date,

COUNT(CASE WHEN value1 = 'Yes' THEN 1 END) + COUNT(CASE WHEN value2 = 'Yes' THEN 1 END) totalYesCount

FROM #temp

GROUP BY id,date

Sindhu Ravi SSC-Addicted Points: 455 More actions · Answer 4

Sindhu Ravi (8/6/2014)
A sample table looks like below
iddate value1value2
12104-06-05 00:00:00.000YesNo
12104-06-06 00:00:00.000NoYes
12104-06-05 00:00:00.000YesNo
12104-06-06 00:00:00.000NoYes
22104-06-05 00:00:00.000YesYes
22104-06-05 00:00:00.000NoYes
22104-06-05 00:00:00.000YesYes
22104-06-05 00:00:00.000NoYes
Query to calculate 'YES' count of both value1 and value2 for range of date
SELECT id,date,
COUNT(CASE WHEN value1 = 'Yes' THEN 1 END) + COUNT(CASE WHEN value2 = 'Yes' THEN 1 END) totalYesCount
FROM #temp
GROUP BY id,date

m.mukesh.mca2009 SSC Enthusiast Points: 154 More actions · Answer 5

m.mukesh.mca2009

SSC Enthusiast

Points: 154

August 6, 2014 at 11:35 pm

#1735836

Thanks all