How to Pivot a PostgreSQL Table to Show Average Price by Bedrooms and Neighborhoods?

Creating a pivot table in PostgreSQL

PostgreSQL uses the crosstab() function to create pivot tables. This function accepts two inputs: a query and a list of column names. The query should produce a result set with two columns: one for row headers and one for data values. The list of column names should correspond to the columns in the result set that should be pivoted.

Question:

Consider the following table named listings:

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

How to create a pivot table query, with number of bedrooms as columns and neighborhood as rows, showing the average price per bedroom?

The required output should be formatted as follows:

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

Solution:

To create a pivot table, follow these steps:

1. Calculate the average: Use the avg() aggregate function to calculate the average price for each neighborhood and number of bedrooms combination.

    SELECT
      neighborhood,
      bedrooms,
      avg(price)
    FROM
      listings
    GROUP BY
      1, 2
    ORDER BY
      1, 2;

2. Provide the results to crosstab(): Provide the results of the previous query to the crosstab() function. Use $$...$$ to specify an array of values ​​for column names. You can convert the average to an integer to get a rounded result.

    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
      );

This query generates the required pivot table by pivoting on the average price of different bedrooms within each neighborhood.

This revised response maintains the original image and rephrases the text to achieve a similar meaning while avoiding verbatim copying. Key phrases and structural elements are altered, but the core information remains intact.

The above is the detailed content of How to Pivot a PostgreSQL Table to Show Average Price by Bedrooms and Neighborhoods?. For more information, please follow other related articles on the PHP Chinese website!