Grouping by intervals

Question

Given a table (mytable) containing a numeric field (mynum), how would one go about writing an SQL query which summarizes the table's data based on ranges of values in that field rather than each distinct value?

For the sake of a more concrete example, let's make it intervals of 3 and just "summarize" with a count(*), such that the results tell the number of rows where mynum is 0-2.99, the number of rows where it's 3-5.99, where it's 6-8.99, etc.

Answer 1

The idea is to compute some function of the field that has constant value within each group you want:

select count(*), round(mynum/3.0) foo from mytable group by foo;

Answer 2

I do not know if this is applicable to mySql, anyway in SQL Server I think you can "simply" use group by in both the select list AND the group by list.

Something like:

select 
    CASE 
        WHEN id <= 20 THEN 'lessthan20' 
        WHEN id >  20 and id <= 30 THEN '20and30' ELSE 'morethan30' END,
    count(*) 
from Profiles 
where 1=1 
group by 
    CASE 
        WHEN id <= 20 THEN 'lessthan20' 
        WHEN id >  20 and id <= 30 THEN '20and30' ELSE 'morethan30' END

returns something like

 column1     column2    
 ----------  ---------- 
 20and30     3          
 lessthan20  3          
 morethan30  13