CODEWITHSUNDEEP
mysql
prageeth
prageeth

Reputation: 7395

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?

Upvotes: 0

Views: 2494

Answers (4)

Alex
Alex

Reputation: 127

You should use functions similar to rank and dense_rank in MSSQL, you can implement them in MySQL starting at the following link:

http://www.folkstalk.com/2013/03/grouped-dense-rank-function-mysql-sql-query.html

Upvotes: 0

splash21
splash21

Reputation: 809

This should give you the output you would like, and includes the ranges with zero in the count column.

SET @rs = 0; SELECT IF(@rs, @rs := @rs + 10, @rs := 1) AS range_start, @rs + 9 AS range_end, (SELECT COUNT(id) FROM manager WHERE account_no >= @rs AND account_no <= @rs + 9) AS `count` FROM manager;

To omit the rows with zero in the count column;

SET @rs = 0; SELECT * FROM (SELECT IF(@rs, @rs := @rs + 10, @rs := 1) AS range_start, @rs + 9 AS range_end, (SELECT COUNT(id) FROM manager WHERE account_no >= @rs AND account_no <= @rs + 9) AS `count` FROM manager) AS data WHERE `count` > 0;

Upvotes: 1

Taryn
Taryn

Reputation: 247810

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

Upvotes: 5

Babblo
Babblo

Reputation: 803

Try with something like this:

SELECT
    CASE
        WHEN range_start < 10 THEN 'Under 10'
        WHEN range_start BETWEEN 11 and 29 THEN '11 - 29'
        (...more ranges...)
    END as range,
    COUNT(*) AS count
    GROUP BY range
    ORDER BY range

Upvotes: 0

Related Questions