postgresql - 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?

question from:https://stackoverflow.com/questions/6669730/sql-query-that-groups-different-items-into-buckets

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.