CODEWITHSUNDEEP
mysqlaggregate-functionshistogram
user3272483
user3272483

Reputation: 408

Grouping Ranges In Rows In Mysql

I have a large number of rows and i would like to know the various ranges of data present.

With this code

select
     count(CASE WHEN the_amount BETWEEN 2000 AND 3000 THEN 1 END) as '2000 to 3000',
     count(CASE WHEN the_amount BETWEEN 3001 AND 4000 THEN 1 END) as '3001 to 4000',
     count(CASE WHEN the_amount BETWEEN 4001 AND 5000 THEN 1 END) as '4001 to 5000',
     count(CASE WHEN the_amount BETWEEN 5001 AND 6000 THEN 1 END) as '5001 to 6000'
 from r_data where transaction_type = 'send'

i am able to count the ranges but this produces only one row.

enter image description here

What i would like is data in this format

+------+------+----------+
| From | To   | count(*) |
+------+------+----------+
|  2000 |    3000 |    3 |
|  3001 |    4000 |    4 |
|  4001 |    5000 |    2 |
|  5001 |    6000 |    1 |
+------+------+----------+

I am trying to write the code like this

SELECT
    2000 as 'from',
    3000 as 'to',
    COUNT(*) as 'occurence'
FROM r_data where transaction_type = 'send'
GROUP BY the_amount;

but this only shows for 2000 and 3000 and does not event give a correct count.How can i write a query to produce the format i want?.

Upvotes: 0

Views: 339

Answers (2)

O. Jones
O. Jones

Reputation: 108776

If you don't know your total val ranges, but you know you need buckets of 1000 in size, you can do this:

SELECT COUNT(*) `count`,
       1000*FLOOR(val/1000) `from`,
       1000*FLOOR(val/1000)+999 `to`
  FROM r_data
 WHERE transaction_type = 'send'
 GROUP BY FLOOR(val/1000)

The expression GROUP BY FLOOR(val/1000) does the trick of aggregating your values into chunks of 1000.

You can also add ORDER BY COUNT(*) DESC to this query to find the biggest chunk of values, and do other such data display manipulation

Upvotes: 1

Eugen Rieck
Eugen Rieck

Reputation: 65304

Try this:

SELECT
  vals.val AS `From`,
  vals.val+999 AS `To`,
  COUNT(*) AS `Count`
FROM
  r_data
  INNER JOIN (
     SELECT 1 AS val
     , UNION SELECT 1001
     , UNION SELECT 2001
     , UNION SELECT 3001
     , UNION SELECT 4001
     , UNION SELECT 5001
     , UNION SELECT 6001
     -- , ....
  ) AS vals 
    ON r_data.the_amount>=vals.val 
    AND r_data.the_amount<(vals.val+1000) 
GROUP BY vals.val

Upvotes: 0

Related Questions