group by range in mysql

user_446668 0
Viewed 39816
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
range group-by mysql
10 Answers

This won't be the exact solution for this question, but it is just similar suggestion for someone other. I need to create the number buckets too, because if I groupped by number i got 9k different values.

I need to have smaller count of groups.

I managed it by grouping by logarithm (and round it). Now instead of 9k groups i have only 18 groups. (Then i will use it for PDF or CDF for 1-x scale score computation).

SELECT COUNT(*) AS `Rows`, round(log(`diff`)) f FROM `users` GROUP BY f ORDER BY f

enter image description here

user_48702730

Here is a more generalized approach to binning in SQL:

SELECT
    concat(
        binsize * floor(diff / binsize),
        ' - ',
        binsize * floor(diff / binsize) + binsize - 1
    ) as range,
    count(*) as number_of_rows
FROM
    new_table,
    (
        SELECT
            21 as binsize
        FROM dual
    ) as prm
GROUP BY 1
ORDER BY floor(diff / binsize)

This way, you only have to provide the size of your range (called bins) once, in the sub query from dual.

The sub query returns a table of size 1 in both dimensions, a single row with a single column. This table is cross tabulated with each row of the other table, so its value is accessible in each row of the first table. This works without specifying a join condition.

As long as you only return a single row, you can add parameters to your sub query. For example, you can define upper and lower bounds to exclude certain features from your result this way:

SELECT
    concat(
        binsize * floor(diff / binsize),
        ' - ',
        binsize * floor(diff / binsize) + binsize - 1
    ) as range,
    count(*) as number_of_rows
FROM
    new_table,
    (
        SELECT
            21 as binsize,
            21 as above,
            83 as below
        FROM dual
    ) as prm
WHERE
    diff >= above
    AND diff <= below
GROUP BY 1
ORDER BY floor(diff / binsize)

If your RDBMS supports it, consider restructuring your query to a CTE (Common Table Expression), which helps in making the expression look neater and more tidy by putting the declaration of parameters right to the start of the whole statement:

WITH prms as (
    SELECT
        21 as binsize,
        21 as above,
        83 as below
    FROM dual
)
SELECT
    concat(
        binsize * floor(diff / binsize),
        ' - ',
        binsize * floor(diff / binsize) + binsize - 1
    ) as range,
    count(*) as number_of_rows
FROM
    new_table, prms
WHERE
    diff >= above
    AND diff <= below
GROUP BY 1
ORDER BY floor(diff / binsize)
user_133700880
Related