How to Insert Select statments in Case clause in SQL

Question

SQL knowledge Beginner

I have a below table: ScoreTable

Name  Score  Reason  Subject 
a1      0     NULL     NULL
a2      -1    NULL     NULL
a3      -3    fail     Maths
a4      -3    fail     History
a3       0    NULL     NULL

I want to write a query which will look some thing like below

Select DISTINCT Name, Result, 
   (If Result = -3 
      then Concat(Reason,' ',Subject))As FailedIn)

From ScoreTable

Expected Output:

Name  Score  FailedIn 
a1      0     0
a2      -1    0
a3      -3    fail Maths
a4      -3    fail History

Answer 1

You probably don't want to put "0" and a string in the same column. It is better to use NULL. So:

Select Name, Result, 
       (case when Result = -3 then Reason + ' ' + Subject
        end) as FailedIn

It is unclear why the last row disappears, but perhaps you want something like:

Select Name, min(Result), 
       (case when min(Result) = -3 then max(Reason + ' ' + Subject)
        end) as FailedIn
from table t
group by name

Answer 2

Try using CASE..WHEN..THEN like below and use minimum result score :

SELECT Name, MIN(Result)
   CASE WHEN Result = -3 
        THEN Reason +' '+Subject 
        ELSE Result 
   END As FailedIn
FROM mytable
GROUP BY Name, Result

Answer 3

Try this

SELECT NAME, SCORE, CASE WHEN SCORE = -3 THEN CONCAT(REASON, ' ' ,SUBJECT ) ELSE '0' END as FAILEDIN FROM ScoreTable