group by price range
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.
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;
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