mysql switch case

Question

I have a query structure like below, Im wondering if there is a way to the write the select queries as one using CASE statements or by some other means so that the values get inserted into the appropriate variables based on their values.

DECLARE passes INT;
DECLARE fails INT;
..

SELECT count(score)
INTO passes
  FROM scores
 WHERE score >= 40;

SELECT count(score)
INTO fails
  FROM scores
 WHERE score < 40;

Murdoch came up with a neat solution to this problem, I just had to make one change to it to put each of values in to there respective variables

SELECT * 
INTO   passes, fails 
FROM  (SELECT SUM(CASE 
                    WHEN score >= 40 THEN 1 
                    ELSE 0 
                  END) AS _passes, 
              SUM(CASE 
                    WHEN score < 40 THEN 1 
                    ELSE 0 
                  END) AS _fails 
       FROM   scores) AS x;

Just as a side note in the logic you've got there a score of 40 wouldn't either pass or fail — iwalkbarefoot, Mar 12 '11 at 13:08
Thanks, the above sql is just an example, the actual data, logic etc that im using is different. — volting, Mar 12 '11 at 13:10
It's not easy, because you're selecting from two different tables. I can't think of any solution that would be more efficient than doing what you're doing, as they'd have to boil down to two different queries "under the hood", and at least what you've got at the moment is simple and easy to read. — Matt Gibson, Mar 12 '11 at 13:26
@Matt Gibson: Im actually selecting from one table, there was a mistake above code, Thanks — volting, Mar 12 '11 at 13:30

score 9 · Accepted Answer · answered Mar 12 '11 at 13:28

9

You can do this by doing a case for each score and returning 1 or 0. Then wrapping the whole thing in a SUM (not a COUNT) in effect adding one for each instance that matches the case.

SELECT 
SUM(CASE WHEN score >= 40 THEN 1 ELSE 0 END) AS passes, 
SUM(CASE WHEN score < 40 THEN 1 ELSE 0 END) AS fails 
FROM scores

answered Mar 12 '11 at 13:28

3urdoch

7,192
8
42
58

Thanks that worked.. I just had to wrap another select statement around to insert the values into the variables. – volting Mar 12 '11 at 14:09

score 3 · Answer 2 · edited Oct 21 '12 at 07:46

3

DECLARE tpasses INT;
DECLARE tfails INT;

SELECT 
    SUM(CASE WHEN score >= 40 THEN 1 ELSE 0 END) AS passes, 
    SUM(CASE WHEN score < 40 THEN 1 ELSE 0 END) AS fails 
INTO tpasses, tfails 
FROM scores

edited Oct 21 '12 at 07:46

Pete Carter

2,691
3
23
34

answered May 31 '12 at 18:36

Scott

31
1

mysql switch case

2 Answers2