CODEWITHSUNDEEP
mysqlcountgroup-by
thelost
thelost

Reputation: 6694

MySQL - Group by range

I need to count the records within value ranges.

For example: for the set 1, 7, 9, 23, 33, 35, 1017

select count(myvalue) group by round(myvalue / 10) gives something like:

0-10  -> 3
10-20 -> 0
20-30 -> 1
30-40 -> 2
1010-1020 -> 1

This works fine. However, I need to set an upper limit, so that MySQL returns 40+ --> 1 ? How can this be achieved ?

Upvotes: 8

Views: 10461

Answers (4)

Jayram
Jayram

Reputation: 19588

select t.myvalue as [range], count(*) as [occurences]
from (
  select myvalue,
   case when myvalue >= 0 and myvalue< 10 then '0-9'
   when myvalue >= 10 and myvalue< 20 then '10-19'
   when myvalue >= 20 and myvalue< 20 then '20-29'
   when myvalue >= 30 and myvalue< 40 then '30-39'
   else '40+' end as range
 from t) t
group by t.myvalue

Upvotes: 4

Zion Gabay
Zion Gabay

Reputation: 37

SELECT case 
   when myvalue >= 0 and myvalue< 10 then '0-9'
   when myvalue >= 10 and myvalue< 20 then '10-19'
   when myvalue >= 20 and myvalue< 20 then '20-29'
   when myvalue >= 30 and myvalue< 40 then '30-39'
   else '40+' 
   end as range
 from t 
group by range

Upvotes: 4

RandomSeed
RandomSeed

Reputation: 29769

I would suggest this solution that borrows from both pilsetnieks and Jayram's solutions:

SELECT
    COUNT(*) AS cnt,
    IF (myvalue >= 40; -1; ROUND(myvalue / 10) AS range
FROM t
GROUP BY range

Upvotes: 2

pilsetnieks
pilsetnieks

Reputation: 10420

You can either sum the values on the client side or use two queries, possibly with union, to fetch the data, e.g.:

select round(myvalue / 10), count(myvalue) from table where myvalue < 40 group by round(myvalue / 10)
union
select '40+', count(myvalue) from table where myvalue >= 40

It is absolutely possible to write it in a single query with subqueries or convoluted conditions but it just wouldn't be as simple and maintainable.

Upvotes: 12

Related Questions