CODEWITHSUNDEEP
mysqlsqlcase-statement
volting
volting

Reputation: 19077

mysql switch case

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;

Upvotes: 4

Views: 25316

Answers (2)

Scott
Scott

Reputation: 31

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

Upvotes: 3

3urdoch
3urdoch

Reputation: 7332

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

Upvotes: 9

Related Questions