sql query that groups different items into buckets

Question

I am trying to write a query that returns the count of items whose price falls into certrain buckets:

For example if my table is:

item_name | price i1        | 2 i2        | 12 i3        | 4 i4        | 16 i5        | 6 

output:

range   | number of item 0 - 10  |  3 10 - 20 |  2 

The way I am doing it so far is

SELECT count(*) FROM my_table Where price >=0 and price <10 

then

SELECT count(*) FROM my_table Where price >=10 and price <20 

and then copy pasting my results each time into excel.

Is there an automatic way to do this in an sql query?

Answer 1

An expanded option from what Kerrek described, you can do you grouping based on a case/when

select       case when price >= 0 and price <= 10    then '  0 - 10'            when price > 10 and price <= 50   then ' 10+ - 50'            when price > 50 and price <= 100  then ' 50+ - 100'            else 'over 100'       end PriceRange,       count(*) as TotalWithinRange    from       YourTable    group by 1 

Here, the "group by 1" represents the ordinal column in your select statement... in this case, the case/when as TotalWithinRange.