00 Votes

MySQL: GROUP BY range - Count small, medium and large data sets

Tip by Stefan Trost | 2013-01-22 at 21:05

Issue: We have two values. For example, 100 and 200. We would like to count all data records from a MySQL table in which a certain value is smaller than 100 or larger than 200 or between 100 and 200 and we want to show the result.

Solution: We can determine the values with the following SQL-query where "tab" is our table, "col" our column we are interested in and "id" an unique identifier of each column (we want to count). The name "range" can be selected freely and is only used as placeholder. 100 and 200 are the cut off values, again they are arbitrary selected.

SELECT 
   COUNT(id),

   IF ( tab.col < 100, 0,
       IF (
           tab.col < 200, 1, 2
       )   
   ) AS range

FROM tab WHERE ... GROUP BY range

A possible result of this query might be:

COUNT(id)    range
10           0
15           1
5            2

So, we have found 10 records smaller than 100, 15 records smaller than 200 and larger or equal 100 as well as 5 records that are larger or equal 200.

How it works: Simply explained, we define a new column "range" for our result. This column is set to 0, 1 or 2 depending on the two IF cases. Whenever our column "col" is smaller than 100, our first IF ensures that "range" is set to 0. If not, we come to the second IF condition, which sets the value of "range" to 1 or 2, depending on whether the value of "col" is smaller than 200. After that, we group the result according to this column "range" so that we get the number of items per range as our result.

ReplyPositiveNegative

About the Author

AvatarYou can find Software by Stefan Trost on sttmedia.com. Do you need an individual software solution according to your needs? - sttmedia.com/contact
Show Profile

 

Related Topics

Important Note

Please note: The contributions published on askingbox.com are contributions of users and should not substitute professional advice. They are not verified by independents and do not necessarily reflect the opinion of askingbox.com. Learn more.

Participate

Ask your own question or write your own article on askingbox.com. That’s how it’s done.