CODEWITHSUNDEEP
mysqlplotgroup-bycount
Avba
Avba

Reputation: 15276

MYSQL - How to group data based on ranges and count it

I'm looking for a way to group MYSql data into buckets (ranges of values) then count how many values were in each group and then plot it on a graph.

I have lots of ranges , not few like here which I would do manually.

For example if my data looks like:

+--------+
| column |
+--------+
|      1 |
|      2 |
|     10 |
|     15 |
|     20 |
|    100 |
|    150 |
|   1000 |
|  10000 |
+--------+

I could create buckets and count the number of values in each bucket:

+-------------+---------------+-----------------+-----------------+
| bucket(0-9) | bucket(10-99) | bucket(100-999) | bucket(1000-1M) |
+-------------+---------------+-----------------+-----------------+
|           2 |             3 |               2 |               2 |
+-------------+---------------+-----------------+-----------------+

Upvotes: 0

Views: 1448

Answers (2)

Strawberry
Strawberry

Reputation: 33945

Here's another idea to play with...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table (i INT NOT NULL PRIMARY KEY);

INSERT INTO my_table VALUES
(1),
(2),
(10),
(15),
(20),
(100),
(150),
(1000),
(10000);

SELECT LENGTH(i) bucket
     , CONCAT(RPAD(1,LENGTH(i),0),'-',RPAD(9,LENGTH(i),9)) range
     , COUNT(*) total 
  FROM my_table 
 GROUP 
    BY bucket;
+--------+-------------+-------+
| bucket | range       | total |
+--------+-------------+-------+
|      1 | 1-9         |     2 |
|      2 | 10-99       |     3 |
|      3 | 100-999     |     2 |
|      4 | 1000-9999   |     1 |
|      5 | 10000-99999 |     1 |
+--------+-------------+-------+

Upvotes: 1

Blank
Blank

Reputation: 12378

If the upper limit is less than 1M, maybe you can try count(case when ... then .. end) based on your sample data and desired result:

select
    count(case when col between 0 and 9 then 1 end) `bucket(0-9)`,
    count(case when col between 10 and 99 then 1 end) `bucket(10-99)`,
    count(case when col between 100 and 999 then 1 end) `bucket(100-999)`,
    count(case when col between 1000 and 1000000 then 1 end) `bucket(1000-1M)`
from test

Check demo in SQLFiddle.

Upvotes: 3

Related Questions