PostgreSQL arrange calculated columns horizontaly

Question

I have a table of real state properties and I want to create a table that shows me the count of the properties that fall in certain price range by zones, something like this:

Zone     0-149k    150-300k
North       25         150
South      150         350

For example for the first result my query would be:

SELECT COUNT(*) FROM MY TABLE
WHERE ZONE = 'North' AND PRICE < 150000

and similar for the other fields

But I'm unable to find a unified query that shows me the data in the desired way. I've tried with the UNION command but this shows me all the data as continuous rows. Any thoughts?

Answer 1

You can use a FILTER on an aggregate:

SELECT
  zone,
  COUNT(*) FILTER (WHERE price < 150000) AS "0-149k",
  COUNT(*) FILTER (WHERE 150000 <= price AND price < 300000) AS "150-300k"
FROM my_table
GROUP BY zone;

(If you have an unknown number of price ranges, see this approach).