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.

Answers


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

Need Your Help

ios play continues sound when app receive push notification

ios apple-push-notifications

Currently i am working on app in which i want to play continues sound when app receives push notification. as i know apple allow to play custom sound of length of 30 sec here is link..

Memory usage using /usr/bin/time for a giving program

linux performance memory time

I Would like to know how can I gather the memory usage of one program/command (eg. gcc ...) using time command.