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

[Former Member]

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.)

[ACE-SAP]

Hi

Just link the sub queries into a select from dual :

SELECT

  (SELECT open_mode FROM v$database) mod,

  (SELECT database_role FROM v$database) rol

FROM

  dual;

Regards