prageeth prageeth - 1 month ago 7
MySQL Question

Split data in to ranges and display count

I am not an expert in MySql. I am trying to split the data in my table in to ranges based on

account_no
. This is my table.

mysql> select * from manager;
+----+-------+------------+
| id | name | account_no |
+----+-------+------------+
| 1 | John | 5 |
| 2 | Peter | 15 |
| 3 | Tony | 18 |
| 4 | Mac | 35 |
| 5 | Max | 55 |
| 6 | Smith | 58 |
+----+-------+------------+


As you see the
account_no
is a positive number. I want to split these records in to batches of 10, based on account_no and display the count in that range.


For an example

between 0 and 10 there is only 1 record

between 11 and 20 there are 2 records

between 21 and 30 there are no records*(So this should be omitted.)*

etc...


Actually I am hoping to get an output like this.


+-------------+-----------+-------+
| range_start | range_end | count |
+-------------+-----------+-------+
| 1 | 10 | 1 | -> because there is 1 record between 1 and 10
| 11 | 20 | 2 | -> because there are 2 records between 11 and 20
| 31 | 40 | 1 | -> because there is 1 record between 31 and 40
| 51 | 60 | 2 | -> because there are 2 records between 51 and 60
+-------------+-----------+-------+


I tried several combinations but all of them give me only one row in the result.
Can anybody help me?

Answer

My suggestion would be to create a table that contains the ranges - startRange and endRange:

CREATE TABLE range_values   (`startRange` int, `endRange` int) ;

INSERT INTO range_values(`startRange`, `endRange`)
VALUES (1, 10), (11, 20), (21, 30), (31, 40), (51, 60);

Once the table is created, then you can easily join on the table to get the count.

select r.startRange,
  r.endRange,
  count(m.account_no) totalCount
from manager m
inner join range_values r
  on m.account_no >=startrange
  and m.account_no <= endrange
group by r.startRange, r.endRange

See SQL Fiddle with Demo.

The benefit of the table is that you are not coding the range values and can easily updated the ranges in a table without having to change your code.

This query return:

| STARTRANGE | ENDRANGE | TOTALCOUNT |
--------------------------------------
|          1 |       10 |          1 |
|         11 |       20 |          2 |
|         31 |       40 |          1 |
|         51 |       60 |          2 |

If you don't want to create a new table, then you can use something similar to the following:

select startrange,
  endrange,
  count(m.account_no) TotalCount
from manager m
inner join
(
  select 1 startRange, 10 endrange union all
  select 11 startRange, 20 endrange union all
  select 21 startRange, 30 endrange union all
  select 31 startRange, 40 endrange union all
  select 41 startRange, 50 endrange union all
  select 51 startRange, 60 endrange
) r
  on m.account_no >=startrange
  and m.account_no <= endrange
group by r.startRange, r.endRange

See SQL Fiddle with demo

Comments