Count based on condition

Ramco Ramco

4y1k

In above scenario 3 records will get display.

1 A 2 2

1 A 0 1

1 A 1 0

CREATE TABLE Persons (LastName varchar(255),FirstName varchar(255),Sample1 varchar(255),Sample2 varchar(255)); 
Insert Into Persons values('1','A','Y','N')
Insert Into Persons values('1','A','Y','Y')
Insert Into Persons values('1','A','N','Y')
Insert Into Persons values('1','A','Y','Y')

Thanks

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke4y ago
Hello,

To get the desired result, please also add following to the Nisha's solution.
order by 4 desc -- Please add this line
Please DO NOT accept my answer.
+2
Nisha Regil4y ago
SELECT [LastName]
,[FirstName],
sum(case when [Sample1] = 'Y' then 1 else 0 end )
,sum(case when [Sample2] = 'Y' then 1 else 0 end )
FROM [dbo].[Persons]
group by [LastName]
,[FirstName],Sample1,Sample2

The above query will give you the desired output.
+2
Sachin Singh4y ago
1. CREATE TABLE Persons (
2. LastName varchar(255),
3. FirstName varchar(255),
4. Sample1 varchar(255),
5. Sample2 varchar(255)
6. );
8. Insert Into Persons values('1','A','Y','N');
9. Insert Into Persons values('1','A','Y','Y');
10. Insert Into Persons values('1','A','N','Y');
11. Insert Into Persons values('1','A','Y','Y');
here what do you want to ahieve? how do you suppose to get 3 rows in result set?