TSQL How to calculate exam result based on values from multiple rows-CodePudding

I need to calculate the exam result of students based on marks from each subject stored in separate rows. Please see the Marks table below.

I'm expecting a result as

The query I tried is coming too lengthy and too many conditions. Please someone help me how to achieve this with a simple TSQL.

CodePudding user response：

If "based on marks" means "based on whether some threshold has been reached", the query like that will help you.

Let's say SID passed exams when sum of marks equals or greater than 123

select sid, 
       case when sum(marks) < 123 then -- replace 123 with the threshold value
         'Failed'
       else
         'Passed'
       end result
  from test_table
 group by sid

here is the db fiddle to play with

CodePudding user response：

As you mentioned based on marks from each subject.

Means if any student have lesser marks in any subject then the student will be considered as failed.

;with test_table(sid, sub, marks) as (
  select 23, 'English', 35 union all
  select 23, 'Maths', 40 union all
  select 23, 'Science', 20 union all
  select 24, 'English', 60 union all
  select 24, 'Maths', 50 union all
  select 24, 'Science', 66
),
final_result as (
select sid, sub, case when marks < 33 then -1 else 0 end result 
from test_table 
)
select final_result.sid,  case when sum(final_result.result) < 0 then 'Failed' else 'Passed' end FinalResult 
from final_result
group by final_result.sid

CodePudding user response：

If you have minimum mark criteria for each subject, then check it with a CASE expression. And then use another CASE expression to check whether the no. pass criteria is matching with the total no.

Query

declare @minMark as int = 35; -- change accordingly

select [sid],
case when (
    sum(case when [marks] >= @minMark then 1 else 0 end) = count(*)
) then 'Pass' else 'Fail' end as [stat]
from [your_table_name]
group by [sid];