Suppose I have a table in Postgres called listings
that looks like this:
id neighborhood bedrooms price
1 downtown 0 256888
2 downtown 1 334000
3 riverview 1 505000
etc.
How do I write a crosstab query that shows the average price per bedrooms as the columns and neighborhoods as the rows?
The output of the query should look something like this (numbers are made up, columns are the bedrooms):
0 1 2 3
riverton 250000 300000 350000 -
downtown 189000 325000 - 450000
First compute the average with the aggregate function avg():
SELECT neighborhood, bedrooms, avg(price)
FROM listings
GROUP BY 1,2
ORDER BY 1,2
Then feed the result to the crosstab()
function as instructed in great detail in this related answer: