Create a pivot table with PostgreSQL

by

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:

More Related Contents:

  1. PostgreSQL Crosstab Query
  2. Aggregate columns with additional (distinct) filters
  3. Dynamic alternative to pivot with CASE and GROUP BY
  4. PostgreSQL: running count of rows for a query ‘by minute’
  5. Two SQL LEFT JOINS produce incorrect result
  6. Pivot on Multiple Columns using Tablefunc
  7. Transpose latest rows per user to columns
  8. Concatenate multiple result rows of one column into one, group by another column [duplicate]
  9. Return multiple columns of the same row as JSON array of objects
  10. How to include “zero” / “0” results in COUNT aggregate?
  11. GROUP BY + CASE statement
  12. Best way to count rows by arbitrary time intervals
  13. Selecting data into a Postgres array
  14. Compute percents from SUM() in the same SELECT sql query
  15. Get the distinct sum of a joined table column
  16. Aggregate a single column in query with many columns
  17. Multiple array_agg() calls in a single query
  18. Transposing an sql result so that one column goes onto multiple columns
  19. PostgreSQL convert columns to rows? Transpose?
  20. Postgres window function and group by exception
  21. Count cumulative total in Postgresql
  22. PostgreSQL 9.3: Dynamic pivot table
  23. Crosstab with a large or undefined number of categories
  24. Truncate timestamp to arbitrary intervals
  25. How do I determine the last day of the previous month using PostgreSQL?
  26. PL/pgSQL checking if a row exists
  27. How much disk-space is needed to store a NULL value using postgresql DB?
  28. SQL comma-separated row with Group By clause
  29. Generate DEFAULT values in a CTE UPSERT using PostgreSQL 9.3
  30. PostgreSQL nested INSERTs / WITHs for foreign key insertions

Leave a Comment