2008R2 SQL Code for case when using the SUM?

45 views Asked by At

How do I code the PASS or FAIL using the case when statement when sum the total?

If score is 46, then pass. If equal or less than 45, then fail.

sum(qa.scripting1+qa.conduct1+qa.conduct2+qa.conduct3) as CScore,

--Fail??


sum(qa.scripting1+qa.conduct1+qa.conduct2+qa.conduct3)/46 as CPASS
2

There are 2 answers

1
Kaushik Thanki On BEST ANSWER
SELECT 
   CASE 
      WHEN sum(ISNULL(qa.scripting1,0)+ISNULL(qa.conduct1,0)+ISNULL(qa.conduct2,0)+ISNULL(qa.conduct3,0) >= 45 THEN "PASS"
      WHEN sum(ISNULL(qa.scripting1,0)+ISNULL(qa.conduct1,0)+ISNULL(qa.conduct2,0)+ISNULL(qa.conduct3,0) <= 44 THEN "FAIL" 
   END 
FROM Data ;
0
Zohar Peled On

Try this:

Case sum(qa.scripting1+qa.conduct1+qa.conduct2+qa.conduct3) when > 45 then 'pass' else 'fail' end