8

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?

2
  • 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.
    – Hannah Vernon
    Commented Dec 2, 2022 at 16:59

3 Answers 3

15

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

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

0
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.