Create a pivot table with PostgreSQL

Question

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

Erwin Brandstetter · Accepted Answer · 2022-04-09T03:16:36.730

40

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:

PostgreSQL Crosstab Query

edited Apr 09 '22 at 03:16

answered Dec 16 '13 at 18:48

Erwin Brandstetter

605,456
145
1,078
1,228

9

@Avishai: (Fully functional) syntax shorthand with positional references, short for `GROUP BY neighborhood, bedrooms` – Erwin Brandstetter Dec 16 '13 at 18:51

score 18 · Answer 2 · edited May 17 '21 at 05:56

18

The best way to build pivot tables in Postgres are CASE expressions.

SELECT neighborhood,
       round(avg((CASE WHEN bedrooms = 0 THEN price END)), 2) AS "0",
       round(avg((CASE WHEN bedrooms = 1 THEN price END)), 2) AS "1",
       round(avg((CASE WHEN bedrooms = 2 THEN price END)), 2) AS "2",
       round(avg((CASE WHEN bedrooms = 3 THEN price END)), 2) AS "3"
FROM listings
GROUP BY neighborhood;

Running this on the question data yields

NEIGHBORHOOD                  0          1          2          3
-------------------- ---------- ---------- ---------- ----------
downtown                 256888     334000       NULL       NULL
riverview                  NULL     505000       NULL       NULL

edited May 17 '21 at 05:56

answered Feb 25 '19 at 00:44

Evan Allen

102
2
7

This particular example of using case statements is incorrect. The statement `round(avg((case when bedroom = 0 then price else 0 end)),2)` means that every apartment with more than 0 bedrooms gets treated as a 0 bedroom with price 0 when taking the average. – Max Rosett Sep 17 '19 at 12:43
1

Look, I did a similar approach here for my transposed/pivoted query using your approach. If I *DONT* add the `avg` function (lets say for `bedroom` in this case) Postgres will complain and say that `bedroom` would need to be grouped by as well in combination with `neighborhood`. Do you have any idea why `avg` removes the additional group by ? – Matheus Felipe Mar 26 '20 at 21:38
@Evan Allen, I agree that this is often simple when you have a set amount of columns to pivot on. I think your query can be a lot simpler however, consider changing it to `CASE WHEN bedrom = 0 THEN price END`, same as `CASE .... ELSE NULL END` so the aggregate function would ignore those. I would suggest an edit but queue is full – CervEd May 16 '21 at 00:07

score 18 · Answer 3 · answered Jun 30 '21 at 08:37

Another solution that implement with filter:

SELECT neighborhood,
   avg(price) FILTER (WHERE bedrooms = 0) AS "0",
   avg(price) FILTER (WHERE bedrooms = 1) AS "1",
   avg(price) FILTER (WHERE bedrooms = 2) AS "2",
   avg(price) FILTER (WHERE bedrooms = 3) AS "3"
FROM listings
GROUP BY neighborhood;

Create a pivot table with PostgreSQL

3 Answers3

Linked

Related