How to Create a Pivot Table Showing Average House Prices by Neighborhood and Bedroom Count in PostgreSQL?

Creating a Pivot Table in PostgreSQL to Show Average House Prices

Problem: How can we generate a summary table showing average house prices, broken down by neighborhood and number of bedrooms?

Solution: This involves a two-step process using PostgreSQL's crosstab function (from the tablefunc extension).

1. Calculate Average Prices by Neighborhood and Bedroom Count: First, we determine the average price for each combination of neighborhood and bedroom count.

SELECT neighborhood, bedrooms, AVG(price) AS average_price
FROM listings
GROUP BY neighborhood, bedrooms
ORDER BY neighborhood, bedrooms;

2. Use the crosstab Function: Next, we feed the results from step 1 into the crosstab function. This function transforms the data into a pivot table. The second argument specifies the bedroom counts we want as columns. Note that you'll need to install the tablefunc extension if you haven't already (CREATE EXTENSION tablefunc;).

SELECT *
FROM crosstab(
    'SELECT neighborhood, bedrooms, AVG(price)::int AS average_price
     FROM listings
     GROUP BY neighborhood, bedrooms
     ORDER BY neighborhood, bedrooms',
    $$SELECT unnest('{0,1,2,3}'::int[]) AS bedrooms$$
) AS ct ("neighborhood" text, "0" int, "1" int, "2" int, "3" int);

This query produces a pivot table with neighborhoods as rows and the average price for 0, 1, 2, and 3 bedrooms as columns. Remember to adjust the '{0,1,2,3}' array to reflect the actual bedroom counts present in your listings table. The ::int cast ensures the average price is treated as an integer; you may need to adjust this based on your price column's data type.

The above is the detailed content of How to Create a Pivot Table Showing Average House Prices by Neighborhood and Bedroom Count in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!