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

Posted on

### 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?

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