CODEWITHSUNDEEP
sqlmysql
Dave Sherohman
Dave Sherohman

Reputation: 46235

Grouping by intervals

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.

Upvotes: 18

Views: 15810

Answers (2)

ila
ila

Reputation: 4724

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

Upvotes: 5

Jouni K. Sepp&#228;nen
Jouni K. Sepp&#228;nen

Reputation: 44152

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;

Upvotes: 19

Related Questions