Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers

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

share|improve this answer
    
Thanks a lot. Will give it a try –  Tarun Aug 16 '12 at 11:37
add comment

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 !!!
share|improve this answer
    
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
add comment

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`;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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