How do I calculate COUNT(*) for a range without needing to use multiple queries in mysql

Question

I am using MySQL and I need help using COUNT(*) for a range of values within a table.

I have a table named PERSON (In the live system I expect several hundred thousand records or more).

Name    Result
a       100
b       130.45
c       182.96
d       65.45
e       199
f       245

I need to query the table to find out how many records belong to a given range. For example, how many persons belong to 0-50 range. Range values are dynamic. So the expected result is something like:

Range       Count
0-50            0
51-100          2
101-150         1
151-200         2
201-250         1

Off course I can do:

SELECT COUNT(*) FROM PERSON WHERE RESULT <= 50
SELECT COUNT(*) FROM PERSON WHERE RESULT > 50 AND RESULT <= 100 

and so on for all ranges...

However, there must be a better, more efficient way to do this?

Answer 1

using the case you can define any range you want.

select 
  case 
    when RESULT between 0 and 50 then '0-50'
    when RESULT between 50 and 100 then '51-100'
    when RESULT between 100 and 150 then '101-150'
    when RESULT between 150 and 200 then '151-200'
    when RESULT between 200 and 250 then '201-250'
    else 'OTHERS'
  end as `Range`,
  count(1) as `Count`
from PERSON
group by `Range`;

Answer 2

Unless I made a mistake in grouping calculation(sorry, I'm not able to test it now), you may use something like

SELECT CONCAT(grp_id*50+IF(grp_id>0,1,0),'-',(grp_id+1)*50) as `Range`, cnt as `Count`
FROM
(
  SELECT floor(IF(Result-1<0,0,Result-1)/50) as grp_id, COUNT(*) as cnt
  FROM PERSON
  GROUP BY floor(IF(Result-1<0,0,Result-1)/50)
 )a

There is no real need for derived table , I use it here just for clarity sake

Answer 3

I hope this does not appear confusing but here is the query you need:

SELECT IF(rng='1 - 50','0 - 50',rng) `Range`,
IFNULL(B.rngcount,0) `Count` FROM
(
    SELECT '1 - 50' rng UNION
    SELECT '51 - 100'   UNION
    SELECT '101 - 150'  UNION
    SELECT '151 - 200'  UNION
    SELECT '201 - 250'
) A LEFT JOIN
(SELECT CONCAT(FLOOR(Result/50)*50+1,' - ',FLOOR(Result/50)*50+50) rng,
COUNT(1) rngcount FROM person GROUP BY rng) B USING (rng);

Here is your sample data from the question

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS person;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE person (name varchar(10),result double);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO person VALUES
    -> ('a',   100),('b',130.45),('c',182.96),
    -> ('d', 65.45),('e',   199),('f',   245);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM person;
+------+--------+
| name | result |
+------+--------+
| a    |    100 |
| b    | 130.45 |
| c    | 182.96 |
| d    |  65.45 |
| e    |    199 |
| f    |    245 |
+------+--------+
6 rows in set (0.00 sec)

mysql>

and here is the query executed:

mysql> SELECT IF(rng='1 - 50','0 - 50',rng) `Range`,
    -> IFNULL(B.rngcount,0) `Count` FROM
    -> (
    ->     SELECT '1 - 50' rng UNION
    ->     SELECT '51 - 100'   UNION
    ->     SELECT '101 - 150'  UNION
    ->     SELECT '151 - 200'  UNION
    ->     SELECT '201 - 250'
    -> ) A LEFT JOIN
    -> (SELECT CONCAT(FLOOR(Result/50)*50+1,' - ',FLOOR(Result/50)*50+50) rng,
    -> COUNT(1) rngcount FROM person GROUP BY rng) B USING (rng);
+-----------+-------+
| Range     | Count |
+-----------+-------+
| 0 - 50    |     0 |
| 51 - 100  |     1 |
| 101 - 150 |     2 |
| 151 - 200 |     2 |
| 201 - 250 |     1 |
+-----------+-------+
5 rows in set (0.00 sec)

mysql>

Give it a Try !!!