group by price range

Question

Say I have a table of real estate properties:

A  15,000
B  50,000
C 100,000
D  25,000

I'd like to group them by 0 - 49,999, 50,000 - 99,999 and 100,000 - 200,000

So the result should be:

   0 -  49k (2)
 50k -  99k (1)
100k - 200k (1)

Is there a way to do that in one SQL statement? I'm using Postgres by the way.

Answer 1

You can GROUP BY an experession, something like that:

SELECT price/50000*50000 AS minPrice, 
    (price/50000+1)*50000-1 AS maxPrice, 
    COUNT(*)
FROM table
GROUP BY price/50000;

Answer 2

Depends on whether you would accept a subselect in the statement and still call it one statement. Assuming that you want your ranges to extend, a subselect with a case statement to set the range, then an outer select grouping by range would work. If all of your ranges were the same size it would be easier as you could just divide by the range size and group by that.

select t.range, count(*) as num
from
   (select case
       when price < 50000 then '0 - 49K'
       when price >= 50000 and price < 100000 then '50 - 99K'
       when price >= 100000 and price < 200000 then '100 - 199K'
       ...
       end
       as range,
       price
       from table) as t
group by range