MySQL Query: Select an int field multiple times based on range the numbers fall into

Question

I have an int field in my table scores. It can be a number between 0 and 100. There are over a 1000 rows in that table. I want to select the number of rows that fall between ranges 0-25, 26-50, 51-75 and 76-100.

So basically, if this is my scores table

score (int)
------------
 10
 20
 12
 56
 43
 90
 87

The output of the query should look like this ( let's call the ranges R1, R2, R3, R4 )

R1   R2   R3   R4
------------------
3    1    1    2

Let me know if something's not clear.

EDIT I got this far

SELECT CASE score
WHEN score BETWEEN 0 AND 25 THEN 'R1'
WHEN score BETWEEN 26 AND 50 THEN 'R2' 
WHEN score BETWEEN 51 AND 75 THEN 'R3' 
ELSE 'R4'
END, COUNT(*)
FROM scores

But when I put a AS range after END, I get an error. (UPDATE: nvm this, it supposed to be AS 'range', with quotes)

Also, if score is 0, it shows up under R2 and not R1. Why is that?

Answer 1

Try something along the lines of the following (caveat: not tested):

SELECT   CASE WHEN score BETWEEN 0 AND 25 THEN 'R1' 
              WHEN score BETWEEN 26 AND 50 THEN 'R2' 
              WHEN score BETWEEN 51 AND 75 THEN 'R3' 
              WHEN score BETWEEN 76 AND 100 THEN 'R4' 
         END AS r,
         COUNT(*)
FROM     scores
GROUP BY r

Right now I cannot remember whether MySQL will let you group by an alias. If not, group by the CASE expression instead.

Updated: It seems that MySQL will, in fact, let you group by an alias.

Answer 2

    create table scores (score int)

    insert into scores values(5);
    insert into scores values(15);
    insert into scores values(25);
    insert into scores values(30);
    insert into scores values(35);
    insert into scores values(40);
    insert into scores values(45);
    insert into scores values(55);
    insert into scores values(65);
    insert into scores values(80);
    insert into scores values(90);
    insert into scores values(95);
    insert into scores values(75);
    insert into scores values(50);


     select SUM(case t.rank when 1 then c else 0 end) as R1,
             SUM(case t.rank when 2 then c else 0 end) as R2,
             SUM(case t.rank when 3 then c else 0 end) as R3,
             SUM(case t.rank when 4 then c else 0 end) as R4
      from
      (         
        select TRUNCATE(score / 26,  0) + 1 rank, count(*) c
         from scores
        group by truncate(score / 26, 0) + 1
      ) t

result:

R1   R2  R3 R4
--------------
3    5    3  3