jeudi 22 août 2019

WHEN condition in MySql genrating multiple column instead of one?

I am having two tables (student and marks) like below screen:

STUDENT TABLE:

enter image description here

MARKS TABLE:

enter image description here

QUERY:

enter image description here

select s.*, m.math+m.English+m.science+m.hindi+m.s_science as Total,
    CASE WHEN m.math < 30 THEN 'Fail in Math' else '' END,
    CASE WHEN m.English < 30 THEN 'Fail in English' else '' END,
    CASE WHEN m.science < 30 THEN 'Fail in science' else '' END,
    CASE WHEN m.Hindi < 30 THEN 'Fail in Hindi' else '' END,
    CASE WHEN m.s_science < 30 THEN 'Fail in s_science' else '' END
AS Remark,

CASE WHEN (m.math+m.English+m.science+m.hindi+m.s_science) < 150 THEN 'Fail' else 'Pass' END AS Result
from marks as m
join student as s
on m.st_id = s.st_id

RESULT:

enter image description here

EXPECTED OUTPUT: Column Remark will create once and final remark will be in siggle column.

Aucun commentaire:

Enregistrer un commentaire