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.

http://stackoverflow.com/questions/232387/in-sql-how-can-you-group-by-in-ranges — Captain Comic, Jan 04 '10 at 13:07

score 10 · Answer 1 · answered Jan 04 '10 at 13:12

10

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;

answered Jan 04 '10 at 13:12

Nicht Verstehen

421
2
11

I suppose you could even use CASE that tvanfosson mentioned in GROUP BY expression avoiding subqueries at the same time. Although you should measure what way performs better. – Nicht Verstehen Jan 04 '10 at 14:00

score 5 · Answer 2 · edited Jan 04 '17 at 10:12

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

group by price range

2 Answers2

Linked