WHEN condition in MySql genrating multiple column instead of one?

Question

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

STUDENT TABLE:

MARKS TABLE:

QUERY:

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:

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

score 1 · Answer 1 · answered Aug 22 '19 at 09:31

You can do it with concat():

select s.*, m.math+m.English+m.science+m.hindi+m.s_science as Total,
  trim(trailing ',' from  concat(
    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

DarkRob · Accepted Answer · 2019-08-22T09:40:01.953

There is syntax issue in your query. Try using this.

If you want to show only one remark.

select s.*, m.math+m.English+m.science+m.hindi+m.s_science as Total,
    CASE WHEN m.math < 30 THEN 'Fail in Math' 
    WHEN m.English < 30 THEN 'Fail in English' 
    WHEN m.science < 30 THEN 'Fail in science'
    WHEN m.Hindi < 30 THEN 'Fail in Hindi'
    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

Otherwise if you want to combine all of the remark in column then use concat. Since there are multiple subject marks.

select s.*, m.math+m.English+m.science+m.hindi+m.s_science as Total,
   Concat( 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

ScaisEdge · Answer 3 · 2019-08-22T09:54:26.747

0

You could try using group_concat for remark based on subquery

  select s.*, t.Total, group_concat(t.remark), t.result  
  from  join student as s 
  INNER JOIN  (
    select s.st_id, 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

    ) t  on t.st_id = s.st_id
  GROUP BY s.st_id

edited Aug 22 '19 at 09:54

answered Aug 22 '19 at 09:30

ScaisEdge

131,976
10
91
107

`Msg 195, Level 15, State 10, Line 21 'group_concat' is not a recognized built-in function name. Msg 102, Level 15, State 1, Line 37 Incorrect syntax near 't'.` showing this error while runnig your code – Amrendra Kumar Aug 22 '19 at 09:53
which db you are using ??? answer updated ..removing wrong space after t. – ScaisEdge Aug 22 '19 at 09:54

score 0 · Answer 4 · answered Aug 22 '19 at 09:35

Try this one like in single case

 select s.*, m.math+m.English+m.science+m.hindi+m.s_science as Total,
        CASE
     WHEN m.math < 30 THEN 'Fail in Math'
        WHEN m.English < 30 THEN 'Fail in English'
         WHEN m.science < 30 THEN 'Fail in science'  
         WHEN m.Hindi < 30 THEN 'Fail in Hindi' 
        WHEN m.s_science < 30 THEN 'Fail in s_science' 
     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

Thorsten Kettner · Answer 5 · 2019-08-22T10:21:43.530

This should maybe just be a remark in the comments section to your request, but is too long for a comment anyway ...

It would probably be better to change the database design to three tables:

student (student_id, name, ...)
class (class_id, name, ...)
student_class (student_id, class_id, mark)

Thus you wouldn't have to know about specific subjects in your queries and you wouldn't have to alter all your queries everytime you remove or add a subject.

select *
from student
join
(
  select
    sc.student_id,
    sum(sc.mark) as total,
    group_concat(case when sc.mark < 30 then c.name end
                 order by c.name separator ', ') as failed_classes,
    sum(sc.mark) < 150 as failed
  from student_class sc
  join class c on c.class_id = sc.class_id
  group by sc.student_id
) evaluated using (student_id)
order by student_id;

WHEN condition in MySql genrating multiple column instead of one?

5 Answers5