7

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 Nov 25 '19 at 15:27
8

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`;
  • This is SO MUCH BETTER than the hacky solutions I've put together for this scenario in the past. Thank you so much for this answer. (T_T)b – Eric L. Oct 5 '16 at 22:59
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 !!!
  • 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
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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