Skip to Content
0
Former Member
Apr 03, 2015 at 09:38 AM

sql query to not include a group in count if a field has particular value

426 Views

Consider a table Student with fields Id,subject and marks and values as

Id Subject Marks 
-- ------- ----- 
1  F   20 
1  S   10 
2  F   25 
2  S   20 
3  F   15 
3  S   10
 ------------------

If a student got Marks=10 in at least one subject, he (his Id) should be considered as Failed else Passed.I want to display two columns as Passed count and Failed count.

As per the scenario my output should be

Passed Failed 
------- -------- 
1    2

I got the output with individual queries for Passed and Failed. But I need single sql query.

select count(distinct f1.Id) as passed

from Student f1 where not exists

(select * from Student f2 where
f2.policy_no=f1.policy_no and f2.Marks=10

)

select count(distinct f1.Id) as failed

from Student f1 where exists

(select * from Student f2 where
f2.policy_no=f1.policy_no and f2.Marks=10

)

Please do the needful. Thanks in advance.

(Note: I don't want the number of subjects each student failed. I want the number of students failed. The output should be only one record.)