Menu
Table: new_table user_number | diff 2 | 0 1 | 28 2 | 32 1 | 40 1 | 53 1 | 59 1 | 101 1 | 105 2 | 108 2 | 129 2 | 130 1 | 144 |(result) v range | number of users 0-20 | 2 21-41 | 3 42-62 | 1 63-83 | 2 84-104 | 1 105-135| 0 136-156| 3 select t.range as [range], count(*) as [number of users] from ( select case when diff between 0 and 20 then ' 0-20' when diff between 21 and 41 then ' 21-41' when diff between 42 and 62 then ' 42-62' when diff between 63 and 83 then ' 63-83' when diff between 84 and 104 then ' 84-104' when diff between 105 and 135 then ' 105-135' else '136-156' end as range from new_table) t group by t.diff Error: You have an error in your SQL syntax, near '[range], count(*) as [number of users] from ( select case when' at line 1 
643
The MySQL GROUP BY Statement The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
The range access method uses a single index to retrieve a subset of table rows that are contained within one or several index value intervals. It can be used for a single-part or multiple-part index.
Group by is one of the most frequently used SQL clauses. It allows you to collapse a field into its distinct values. This clause is most often used with aggregations to show one value per grouped field or combination of fields.
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) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
