Mike Mike - 6 months ago 9
SQL Question

MySQL combines results multiple results values into 2 only

i'm trying to combine values like if 0 then is the first value and if 1, 2 or 3 then it is second value

id student IQ
1 one 0
2 two 1
3 three 2
4 four 3
5 five 0


What I want is to tell me I have 2 student with
IQ=0
and 3 students are
> 0


What is the easiest way to write this mysql query?

Answer

You can try:

SELECT CASE WHEN IQ = 0 THEN 1 
              WHEN IQ > 0 THEN 2
         END,
         COUNT(*)
FROM mytable
GROUP BY CASE WHEN IQ = 0 THEN 1 
              WHEN IQ > 0 THEN 2
         END

or, if IQ is a positive number:

SELECT IF(IQ = 0, IQ, 1),
       COUNT(*)
FROM mytable
GROUP BY IF(IQ = 0, IQ, 1)
Comments