query to obtain final status as fail when any one of listed subject is failed

Question

Select count(course),course,dept, Case When ****condition*** then 'passed' Else 'failed' End as finalstatus from college group by course,dept;

Here condition should check each subject of each student if any student fails atleast one subject then final status should be failed or if student passes all subjects then final status should be passed

Answer 1

You need to use all columns in group by which are present in the select statement.

Answer 2

Assuming that your CASE condition relies on columns different from course and dept, you may need to include your CASE expression in GROUP BY clause:

Select count(course)
       course,
       dept,
       Case When ****condition*** then 
            'passed'
        Else 
            'failed'
       End as finalstatus
from college
group by course,
         dept,
         Case When ****condition*** then 
              'passed'
          Else 
              'failed'
         End;