Price range group

Question

Let's say I have a table of real estate properties:

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

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

Thus, the result should be:

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

Is there a way to do this in a single SQL statement? By the way, I'm using Postgres.

Answer 1

You can GROUP BY experiment, something like this:

 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 accept the subtitle in the instructions and still call it one statement. Assuming you want your ranges to expand, a subtitle with a case argument to set the range, then an external range selection group will work. If all your ranges were the same size, that would be easier since you could just divide by the size and group of the range.

 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