MYSQL CASE flow control statement

Databases
1

I have been trying to make a CASE flow control statement work but keep having problems with syntax errors. Can someone explain the syntax errors and how to determine errors with writing control statements?

SELECT 
	CASE each_rating
		WHEN rating='G',rating='S',rating='B',rating='P'
		THEN SELECT rating;
		WHEN rating='G' 
		THEN SELECT rating='G' ORDER BY project;	
		WHEN rating = 'S' 
		THEN SELECT rating ='S' ORDER BY project;
		WHEN rating ='B'
		THEN SELECT rating = 'B' ORDER BY project;
		WHEN rating ='P'
		THEN SELECT rating = 'P' ORDER BY project;
	FROM student_project_evaluation
	END CASE;
2

Can you describe what you’d want the query to do?

3 
SELECT project
     , CASE WHEN rating IN ('G','S','B','P')
            THEN rating
            ELSE 'X' END AS each_rating  
  FROM student_project_evaluation
ORDER 
    BY project
4

I’m trying to query a database of student stem projects that are to receive a gold, silver, bronze or participation award. The db results have to be real-time, fast and accurate. The db is sorted by project, rating and judge, rating. There are two-judge ratings per student project. For example, if a project gets a ‘G’ and ‘B’ then the students will receive an ‘S’. The table is first sorted by rating G,S,B,P to see how the projects are being rated and check the data. Then the table is sorted by judge and project to get the combined score for each project and a final rating. Each student receives an award at the end of the expo. It is a lot of fun for the kids but stressful trying to figure out the project rating. the query is to show the result at the end of the expo. It is a query of final results that is the problem for me. The CASE flow control query is to sort all the results and show the final project results. Hope this helps.

5

not really…

did you run my query? any comments?

6

yes, that works. I will need to expand it cover all possible outcomes, Thanks.

7

I created another CASE grouping reviewer rating but the syntax is wrong and not showing the expected group_rating. Eventually, there will need to be a CASE for all possible combinations of reviewer ratings.

SELECT reviewer,rating,project,number_of_students AS no
, CASE WHEN reviewer IN (‘G’)
THEN rating = ‘G’
CASE WHEN reviewer IN (‘G’,‘S’)
THEN rating = ‘G’
CASE WHEN reviewer IN (‘G’,‘B’)
THEN rating = ‘S’
CASE WHEN reviewer IN (‘G’,‘P’)
THEN rating = ‘B’
ELSE ‘X’ END AS group_rating
FROM student_project_evaluation
ORDER
BY project,group_rating

8

Based on your description in post #4, that’s not going to work unless both reviews are on the same record.

So essentially, you’re going to need to a select statement which returns something like this

project, reviewer1, rating1, reviewer2, rating2, number_of_students

Once you can get your data into that format, you would need to use a nested case, but you could do something like:

-- WARNING:  Not tested.  Review and test thoroughly!!!
SELECT project
     , number_of_students
     , CASE WHEN rating1 = 'G'
            THEN CASE WHEN rating2 IN ('G', 'S') THEN 'G'
                       CASE WHEN rating2 = 'B' THEN 'S'
                       CASE WHEN rating2 = 'P' THEN 'B'
                       ELSE 'X' END
       CASE WHEN rating1 = 'S'
            THEN CASE WHEN rating2 = 'G' THEN 'G'
                       CASE WHEN rating2 IN ('S', 'B') THEN 'S'
                       ELSE 'X' END
       CASE WHEN rating1 = 'B'
            THEN CASE WHEN rating2 = 'G' THEN 'S'
                       CASE WHEN rating2 IN ('S', 'B') THEN 'B'
                       ELSE 'X' END
       CASE WHEN rating1 = 'P'
            THEN CASE WHEN rating2 = 'G' THEN 'B'
                       ELSE 'X' END
        END AS GROUP_RATING
   FROM student_project_evaluation
  ORDER BY project, group_rating

OR, since you have almost an averaging system in place, a better idea might be to have a lookup table which has a numerical value to it where the group ratings can be found via some math in the joins. It’s an extra table lookup, but gets the ugliness of nested cases out of the way

rating pts grp_min_pts grp_max_pts
G       6       10          12
S       4        6           8
B       2        0           4
P       0        0           0

Then you could do it something like this

-- WARNING:  Not tested.  Review and test thoroughly!!!
SELECT project
     , number_of_students
     , COALESCE(group.rating, 'X') AS group_rating
  FROM student_project_evaluation e
  LEFT OUTER JOIN medal mr1 ON e.rating1 = mr1.rating
  LEFT OUTER JOIN medal mr2 ON e.rating2 = mr2.rating
  LEFT OUTER JOIN medal group ON (mr1.pts + mr2.pts) BETWEEN group.grp_min_pts AND group.grp_max_points
 ORDER BY project, group_rating
9

I like you ideas and think they will work but I think I’m stuck with what I have.
This is a copy of some data,

36%20AM
36%20AM720×421 63.9 KB

10

@patgovan - OK, off the cuff (nor tested for validity), but the basic idea is there…

This is assuming there are only ever 2 reviewers. The sub-query selects the lowest ID on the table as the first review for a project, and the highest for the second. It then is joining in the table two other times just to get the separate ratings.

The only troublesome situation is if you’ve got a number of reviews other than two. One review would select the same review twice, and if you’ve got more, then it would miss some of the reviews.

SELECT e.project
     , e.number_of_students
     , reviewer1,rating
     , reviewer2.rating
     , COALESCE(group.rating, 'X') AS group_rating
  FROM student_project_evaluation e
 INNER JOIN (SELECT project
                  , MIN(id) AS reviewer1ID
                  , MAX(id) AS reviewer2ID
               FROM student_project_evalution 
              GROUP BY project) AS reviewers ON reviewers.project = e.project
 INNER JOIN student_project_evaluation reviewer1 ON reviewer1.ID = reviewers.reviewer1ID
 INNER JOIN student_project_evaluation reviewer2 ON reviewer2.ID = reviewers.reviewer2ID
  LEFT OUTER JOIN medal mr1 ON reviewer1.rating = mr1.rating
  LEFT OUTER JOIN medal mr2 ON reviewer2.rating = mr2.rating
  LEFT OUTER JOIN medal group ON (mr1.pts + mr2.pts) BETWEEN group.grp_min_pts AND group.grp_max_points
 ORDER BY project, group_rating
11

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.