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	189000
2	downtown	3	450000
3	riverview	1	300000
4	riverview	0	250000
5	downtown	1	325000
6	riverview	2	350000

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 have the following format:

neighborhood	0	1	2	3
downtown	189000	325000	-	450000
riverview	250000	300000	350000	-

etc.

Erwin Brandstetter · Accepted Answer · 2024-01-18 23:38:34Z

47

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 (provided by the additional module tablefunc). Cast the avg to int if you want rounded results as displayed:

SELECT *
FROM   crosstab(
   'SELECT neighborhood, bedrooms, avg(price)::int
    FROM   listings
    GROUP  BY 1, 2
    ORDER  BY 1, 2;'

  , $$SELECT unnest('{0,1,2,3}'::int[])$$
   ) AS ct ("neighborhood" text, "0" int, "1" int, "2" int, "3" int);

fiddle

Detailed instructions:

PostgreSQL Crosstab Query

The same can be achieved with the aggregate FILTER clause. A bit simpler, and doesn't need an additional module, but typically slower. Related answer with side-by-side solutions:

Conditional SQL count

edited Jan 18, 2024 at 23:38

answered Dec 16, 2013 at 18:48

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Erwin Brandstetter Over a year ago

@Avishai: (Fully functional) syntax shorthand with positional references, short for GROUP BY neighborhood, bedrooms

julaine May 5 at 8:35

The need to input {0,1,2,3} is unfortunate, but the 'detailed instructions' don't avoid this either.

moraei · Accepted Answer · 2021-06-30 08:37:20Z

41

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;

answered Jun 30, 2021 at 8:37

moraei

1,7831 gold badge19 silver badges18 bronze badges

Comments

Evan Allen · Accepted Answer · 2021-05-17 05:56:26Z

24

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, 2021 at 5:56

user330315

answered Feb 25, 2019 at 0:44

Evan Allen

1622 silver badges7 bronze badges

Collectives™ on Stack Overflow

Create a pivot table with PostgreSQL

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related