group by range in mysql

by

Here is general code to group by range since doing a case statement gets pretty cumbersome.

The function ‘floor’ can be used to find the bottom of the range (not ’round’ as Bohemian used), and add the amount (19 in the example below) to find the top of the range. Remember to not overlap the bottom and top of the ranges!

mysql> create table new_table (user_number int, diff int);
Query OK, 0 rows affected (0.14 sec)

mysql>  insert into new_table values (2, 0), (1, 28), (2, 32), (1, 40), (1, 53),
        (1, 59), (1, 101), (1, 105), (2, 108), (2, 129), (2, 130), (1, 144);
Query OK, 12 rows affected (0.01 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql> select concat(21*floor(diff/21), '-', 21*floor(diff/21) + 20) as `range`,
       count(*) as `number of users` from new_table group by 1 order by diff;
+---------+-----------------+
| range   | number of users |
+---------+-----------------+
| 0-20    |               1 |
| 21-41   |               3 |
| 42-62   |               2 |
| 84-104  |               1 |
| 105-125 |               2 |
| 126-146 |               3 |
+---------+-----------------+
6 rows in set (0.01 sec)

More Related Contents:

  1. Using LIMIT within GROUP BY to get N results per group?
  2. Error related to only_full_group_by when executing a query in MySql
  3. SELECT list is not in GROUP BY clause and contains nonaggregated column …. incompatible with sql_mode=only_full_group_by
  4. How to populate a table with a range of dates?
  5. MySQL Query GROUP BY day / month / year
  6. MySQL order by before group by
  7. Group by clause in mySQL and postgreSQL, why the error in postgreSQL?
  8. Grouping into interval of 5 minutes within a time range
  9. Retrieving the last record in each group – MySQL
  10. MySQL – Selecting a Column not in Group By
  11. Mysql query to dynamically convert rows to columns
  12. MySQL “Group By” and “Order By”
  13. SELECT / GROUP BY – segments of time (10 seconds, 30 seconds, etc)
  14. MySQL – Control which row is returned by a group by
  15. MySQL Order before Group by
  16. MySQL Group By and Sum total value of other column
  17. What does SQL clause “GROUP BY 1” mean?
  18. Using ORDER BY and GROUP BY together
  19. MySQL – Fetching lowest value
  20. ORDER BY date and time BEFORE GROUP BY name in mysql
  21. How to select the first row for each group in MySQL?
  22. GROUP BY – do not group NULL
  23. MySQL GROUP BY DateTime +/- 3 seconds
  24. Sum results of a few queries and then find top 5 in SQL
  25. Select all months within given date span, including the ones with 0 values
  26. MySQL SELECT most frequent by group
  27. sql group by versus distinct
  28. Select only last value using group by at mysql
  29. How do I fix the ‘Out of range value adjusted for column’ error?
  30. SQL query with avg and group by

Leave a Comment