5
\$\begingroup\$

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.

\$\endgroup\$
4
  • 3
    \$\begingroup\$ 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 \$\endgroup\$
    – Malachi
    Dec 1, 2013 at 17:19
  • \$\begingroup\$ Added Fiddle for MySQL - it messes up the dates: sqlfiddle.com/#!2/7afb6/1 (there should be two dates for MDATE)) \$\endgroup\$
    – rolfl
    Dec 2, 2013 at 4:49
  • \$\begingroup\$ Added Fiddle for SQLServer - it fails with group-by problems: sqlfiddle.com/#!6/4ea0f \$\endgroup\$
    – rolfl
    Dec 2, 2013 at 4:50
  • \$\begingroup\$ @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. \$\endgroup\$
    – Mawia HL
    Dec 2, 2013 at 8:46

2 Answers 2

8
\$\begingroup\$

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.

\$\endgroup\$
2
  • \$\begingroup\$ Thank you. But What do you mean by feature best avoided? Does it mean there can be any possible error in the query? \$\endgroup\$
    – Mawia HL
    Nov 30, 2013 at 18:52
  • 3
    \$\begingroup\$ The relational model is deterministic. SQL is deterministic. MySQL's GROUP BY is not deterministic. \$\endgroup\$ Nov 30, 2013 at 19:13
4
\$\begingroup\$

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

\$\endgroup\$
5
  • \$\begingroup\$ 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? \$\endgroup\$
    – rolfl
    Dec 1, 2013 at 12:05
  • \$\begingroup\$ @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 \$\endgroup\$
    – Malachi
    Dec 1, 2013 at 16:43
  • 1
    \$\begingroup\$ 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 \$\endgroup\$
    – rolfl
    Dec 1, 2013 at 17:04
  • \$\begingroup\$ @Malachi I need to use Group because one subject appears more than once in the subject column. \$\endgroup\$
    – Mawia HL
    Dec 2, 2013 at 5:52
  • \$\begingroup\$ 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 \$\endgroup\$
    – Malachi
    Jan 21, 2014 at 20:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.