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

-

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

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 !!!
``````
-
Awesome, Thanks Rolando. Will surely try this. Must admit that my sql skills are not that good. Would the query be any simpler if range is split into two columns so result is 3 column: startrange, endrange, count. Also I have to provide these at run time i.e. startrange and endrange will vary for each user – Tarun Aug 16 '12 at 11:39

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

-
Thanks a lot. Will give it a try – Tarun Aug 16 '12 at 11:37