Exam score query for many types of exams

Question

I have the following MySQL SELECT statement. It is working fine except that the code is too long. I have been looking throughout the Internet to figure out how I can make it shorter.

 SELECT regd, Subject, Section, date, 
 SUM(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,
 SUM(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, 
 SUM(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, 
 SUM(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, 
 SUM(CASE
 WHEN Name_of_exam = 'Final Unit Exam' 
 THEN (Mark_score / Full_mark) *25
 END ) 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 / Full_mark) *25
 END ) AS cce_scored, 
 SUM(CASE
 WHEN Name_of_exam = 'CCE' 
 THEN (Full_mark) END ) AS cce_fm 
 FROM exam_mark 
 WHERE regd='23' AND Section='A' 
 AND date BETWEEN '2013-11-01' AND '2013-11-15'
 GROUP BY Subject

Any suggestion is welcome.

Answer 1

This kind of expression

 (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') 

can usually be shortened to an IN clause.

(Name_of_exam in ('First Term Weekly Test', 'Second Term Weekly Test', etc.))

But there's enough conditional logic in here that I'd have to ask myself, "Am I trying to write a report in SQL?" Reports are better implemented with a report writer.

This doesn't have to do with your question, but a GROUP BY statement that includes only one of 'n' unaggregated columns is almost always a syntax error in standard SQL. MySQL treats this like a feature, but it's a feature best avoided.

It's almost always a syntax error, because standard SQL allows you to group on one of 'n' unaggregated colums when the unaggregated columns are functionally dependent on the grouped column.

Answer 2

You actually don't have to use the SUM function. I re-wrote the first part of your query.

SELECT 
    regd
  , Subject
  , Section
  , date
  , CASE WHEN (Name_of_exam IN ('First Unit Exam','Second Unit Exam','Third Unit Exam')) 
    THEN (Mark_score / Full_mark) *25 END) AS t_scored
  , CASE WHEN (Name_of_exam IN 'First Unit Exam','Second Unit Exam','Third Unit Exam')) 
    THEN (Full_mark) END) AS t_fm

I also implemented the accepted answer as well.

I think that losing the SUM function will also speed up your Query.

---

There isn't really a reason to Group By Subject you probably want to ORDER BY Subject