CODEWITHSUNDEEP
mysqlgroup-bycase
The Reaper
The Reaper

Reputation: 65

Multiple Case statement not working with group by

I am completely new to mysql and do not have any education in programming. I learn everything by myself by searching tutorials and by reading answers here in stackoverflow. My problem here is that my statement does not output anything nor any error. So I total don't understand what's happening in the code. As my schema and statement are a bit long, I made an sqlfiddle here so that you can test yourself.

Here is the output I expect. Please help me.

enter image description here

Here is my attempted query:

SELECT regd, Subject, Section, Test_date,
 (CASE WHEN (Name_of_exam = 'First Unit Exam'
 OR Name_of_exam = 'Second Unit Exam'
 OR Name_of_exam = 'Third Unit Exam')
 THEN (Mark_score / Full_mark) *25 END) AS t_scored,
 (CASE
 WHEN (Name_of_exam = 'First Unit Exam'
 OR Name_of_exam = 'Second Unit Exam'
 OR Name_of_exam = 'Third Unit Exam')
 THEN (Full_mark) END) AS t_fm,
 (CASE
 WHEN (Name_of_exam = 'First Term Weekly Test'
 OR Name_of_exam = 'Second Term Weekly Test'
 OR Name_of_exam = 'Third Term Weekly Test'
 OR Name_of_exam = 'Final Term Weekly Test')
 THEN (Mark_score / Full_mark) *25
 END ) AS w_scored,
 (CASE
 WHEN (Name_of_exam = 'First Term Weekly Test'
 OR Name_of_exam = 'Second Term Weekly Test'
 OR Name_of_exam = 'Third Term Weekly Test'
 OR Name_of_exam = 'Final Term Weekly Test')
 THEN (Full_mark) END ) AS w_fm,
 (CASE
 WHEN Name_of_exam = 'Final Unit Exam'
 THEN (Mark_score / Full_mark) *25
 END ) AS f_scored,
 (CASE
 WHEN Name_of_exam = 'Final Unit Exam'
 THEN (Mark_score) END ) AS score_m,
 (CASE
 WHEN Name_of_exam = 'CCE'
 THEN (Mark_score / Full_mark) *25
 END ) AS cce_scored,
 (CASE
 WHEN Name_of_exam = 'CCE'
 THEN (Full_mark) END ) AS cce_fm
 FROM exam
 WHERE regd='2321' AND Section='A'
 AND Test_date BETWEEN '2013-11-01' AND '2013-11-15'
 GROUP BY Subject

Update: There are three unit exams, four weekly tests, one final exam and one CCE from field Name_of_exam. 25% of the three unit exams namely First Unit Exam, Second Unit Exam, Third Unit Exam needs to be calculated, which can be calculated of course by dividing the sum of mark scored on the three unit exams by the sum of full_marks of the three unit exams. Secondly, We have four weekly test viz., First Term Weekly Test, Second Term Weekly Test, Third Term Weekly Test and Final Term Weekly Test. And a student can have more than one test in the same subject in all of the weekly tests. I want to calculate 25% of the sum of mark_scored from all of the weekly tests, which can be calculated by dividing the sum of mark scores from all the tests by the sum of fullmarks from all the weekly tests. And I want to take out 25% both from CCE and Final Unit Exam. We have four 25% which means 100 in total.

Upvotes: 0

Views: 364

Answers (1)

Barmar
Barmar

Reputation: 782181

This is the query:

SELECT regd, Subject, Section, Test_date,
       SUM(CASE WHEN Name_of_exam IN ('First Unit Exam', 'Second Unit Exam', 'Third Unit Exam')
                THEN Mark_score
           END)/SUM(CASE WHEN Name_of_exam IN ('First Unit Exam', 'Second Unit Exam', 'Third Unit Exam')
                         THEN Full_Mark
                    END)*25 AS t_scored,
       SUM(CASE WHEN Name_of_exam IN ('First Unit Exam', 'Second Unit Exam' 'Third Unit Exam')
                THEN (Full_mark)
           END) AS t_fm,
       SUM(CASE WHEN Name_of_exam IN ('First Term Weekly Test', 'Second Term Weekly Test', 'Third Term Weekly Test', 'Final Term Weekly Test')
                THEN Mark_score
           END)/SUM(CASE WHEN Name_of_exam IN ('First Term Weekly Test', 'Second Term Weekly Test', 'Third Term Weekly Test', 'Final Term Weekly Test')
                         THEN Full_Mark
                    END)*25 AS w_scored,
       SUM(CASE WHEN Name_of_exam IN ('First Term Weekly Test', 'Second Term Weekly Test', 'Third Term Weekly Test', 'Final Term Weekly Test')
                THEN (Full_mark)
           END) AS w_fm,
       SUM(CASE WHEN Name_of_exam = 'Final Unit Exam'
                THEN Mark_score
           END)/SUM(CASE WHEN Name_of_exam = 'Final Unit Exam'
                         THEN Full_Mark
                    END)*25 AS f_scored,
       SUM(CASE WHEN Name_of_exam = 'Final Unit Exam'
                THEN (Mark_score)
           END) AS score_m,
       SUM(CASE WHEN Name_of_exam = 'CCE'
                THEN Mark_score
           END)/SUM(CASE WHEN Name_of_exam = 'CCE'
                         THEN Full_Mark
                    END)*25 AS cce_scored,
       SUM(CASE WHEN Name_of_exam = 'CCE'
                THEN (Full_mark)
           END) AS cce_fm
FROM exam
WHERE regd='2321' AND Section='A'
GROUP BY Subject

FIDDLE

Upvotes: 1

Related Questions