Mysql switch case

Question

I have a query structure as shown below. Im wonders if there is a way to write select queries as one using CASE statements or some other way so that the values ​​are inserted into the corresponding 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 the values ​​in the corresponding 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; 

Answer 1

You can do this by completing the case for each point and returning 1 or 0. Then wrap it all in SUM (not COUNT), essentially adding one for each instance that matches this 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 

Answer 2

 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