group by range in mysql

问题:

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  

回答1:

Mysql as a delimiter for keywords uses backtick sign " ` ", not square brackets (like sql server)

回答2:

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)

回答3:

Here's a solution that will work for any magnitude of diff:

select
  concat(21 * round(diff / 21), '-', 21 * round(diff / 21) + 20) as `range`,
  count(*) as `number of users`
from new_table
group by 1
order by diff;

Here's some testable code and its output:

create table new_table (user_number int, diff int);
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); 
-- run query, output is: 
+---------+-----------------+
| range   | number of users |
+---------+-----------------+
| 0-20    |               1 |
| 21-41   |               1 |
| 42-62   |               2 |
| 63-83   |               2 |
| 105-125 |               3 |
| 126-146 |               2 |
| 147-167 |               1 |
+---------+-----------------+

回答4:

If you have regular ranges, a quicker solution would be to group with the help of div function.

For instance:

select diff div 20 as range, sum(user_number)
from new_table
group by diff div 20;

Ranges are represented as single digits in that case and you have to know what they mean: 0 = 0-19, 1 = 20-39, 2 = 40-59,...

If you need different ranges use different divider or maybe subtract some number from diff. For instance "(diff - 1) div 10" gives you ranges 1-10, 11-20, 21-30,...

回答5:

range is a mysql keyword. You should "scape" it using ´ :

select t.`range` as [`range`], ...

回答6:

One obvious mistake : Mysql uses backticks(

`

), not [](as sqlserver) . Change t.range as [range], count(*) as [number of users] to

t.range as `range`, count(*) as `number of users`

回答7:

You might want to check Are square brackets valid in an SQL query?

I suspect that '[' and ']' are used in Microsoft's SQL but not mysql.

回答8:

select 
case
when diff between 0 and 20 then ' 0-20'
when diff between 0 and 20 then ' 21-41'
when diff between 0 and 20 then ' 42-62'
when diff between 0 and 20 then ' 63-83'
when diff between 0 and 20 then ' 84-104'
when diff between 0 and 20 then ' 105-135'
else '136-156'
end; as 'range',
count(*) as 'number of users'


from new_table
group by range