CODEWITHSUNDEEP
mysqlsql
Rich Bradshaw
Rich Bradshaw

Reputation: 72975

Selecting and grouping on ranges

I have some data like this:

+----+--------+-------+
| id | height | width |
+----+--------+-------+
|  1 |   1000 |   300 |
|  2 |   1024 |   330 |
|  3 |    600 |   200 |
|  4 |    624 |   311 |
|  5 |    724 |   511 |
|  6 |    300 |   200 |
+----+--------+-------+

with many more rows.

I'd like to run a query that does something like this:

Count how many rows have a height between 0 and 400, between 401 and 600, between 601 and 1000, and 1000+

In this case, I'd want it to return something like this:

+-------+---------+----------+-------+
| 0-400 | 401-600 | 601-1000 | 1000+ |
+-------+---------+----------+-------+
|     1 |       1 |        3 |     1 |
+-------+---------+----------+-------+

I'm going to hard code the ranges.

Currently, I'm planning to run a query for each range, is there a better way?

Upvotes: 1

Views: 54

Answers (2)

geoB
geoB

Reputation: 4704

Try something like this:

select sum(if(height<400,1,0)) '<400', sum(if(height>=400 and height<600,1,0)) '400-600'...

g

Upvotes: 2

Andomar
Andomar

Reputation: 238048

select  sum(case when height between 0 and 400 then 1 end) as [0-400]
,       sum(case when height between 401 and 600 then 1 end) as [401-600]
,       ...
from    YourTable

Upvotes: 3

Related Questions