# Exam score query for many types of exams

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.

• this Query appears to be untested and not working correctly, if it is working correctly please show results, table schema, etc so that we can set up a SQLFiddle – Malachi Dec 1 '13 at 17:19
• Added Fiddle for MySQL - it messes up the dates: sqlfiddle.com/#!2/7afb6/1 (there should be two dates for MDATE)) – rolfl Dec 2 '13 at 4:49
• Added Fiddle for SQLServer - it fails with group-by problems: sqlfiddle.com/#!6/4ea0f – rolfl Dec 2 '13 at 4:50
• @rolfl, my code is working fine in mysql 5 any way. I used it in my project, and I found no problem except that it is very long. – Mawia HL Dec 2 '13 at 8:46

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.

• Thank you. But What do you mean by feature best avoided? Does it mean there can be any possible error in the query? – Mawia HL Nov 30 '13 at 18:52
• The relational model is deterministic. SQL is deterministic. MySQL's GROUP BY is not deterministic. – Mike Sherrill 'Cat Recall' Nov 30 '13 at 19:13

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

• Uhm, you do Need to have the SUM... this makes no sense... The OP has a broken query (group-by not matching the un-aggregated columns) now this suggestion to not use sum at all? – rolfl Dec 1 '13 at 12:05
• @rolfl, why do you need SUM? he will get the same data for every cell in that column, the original query is bad with the SUM in it, the GROUP BY is wrong as well, OP probably wants an ORDER BY – Malachi Dec 1 '13 at 16:43
• You and I are looking at it from different sides. Perhaps we see the OP as having different objectives. Essentially his OP select is broken, and there is thus no correct answer – rolfl Dec 1 '13 at 17:04
• @Malachi I need to use Group because one subject appears more than once in the subject column. – Mawia HL Dec 2 '13 at 5:52
• if you are grouping by subject that means you don't want one of them show up or you want them to be merged. so this means that the subject column data is not as atomic as it should be. pretty sure that you need to fix the data in the table to get rid of this Duplicate in the Subject field. if you showed us more of what is going on with the Database and the tables involved it would be easier to say exactly what you need to do with it – Malachi Jan 21 '14 at 20:13