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

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?

• i think, the best would be put your ranges in a temp table. there you can build indices. as a result the joins should perform better than a large case-statement.
– emfi
Commented Nov 25, 2019 at 15:27
• @emfi Comments should only be used for asking for clarification, or to leave constructive criticism that guides the author in improving the post, or to add relevant but minor or transient information to a post (e.g. a link to a related question, or an alert to the author that the question has been updated), or to provide site usage guidance. See the help for details. Commented Dec 2, 2022 at 16:59

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;


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

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 !!!