How to Create a Pivot Table in PostgreSQL to Summarize Average Housing Prices by Neighborhood and Number of Bedrooms?

Generating Pivot Tables in PostgreSQL to Analyze Housing Prices

PostgreSQL offers powerful capabilities for data summarization, including the creation of pivot tables. This example demonstrates how to generate a pivot table showing average housing prices grouped by neighborhood and number of bedrooms.

Step 1: Calculate Average Prices per Neighborhood and Bedroom Count

First, we calculate the average price for each unique combination of neighborhood and bedroom count:

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

This query groups the listings table data by neighborhood and bedrooms, calculating the average price for each group. The results are then ordered for clarity.

Step 2: Pivot the Data Using crosstab()

To transform the aggregated data into a pivot table format, we utilize the crosstab() function:

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

The crosstab() function takes two arguments: the SQL query providing the aggregated data (nested in this case for clarity), and a query defining the categories for the pivot table columns (here, representing the number of bedrooms: 0, 1, 2, and 3). The resulting table alias ct is assigned column names accordingly.

Step 3: Interpreting the Results

The output pivot table will resemble this:

<code>neighborhood  | 0       | 1       | 2       | 3
----------------+---------+---------+---------+---------
downtown      | 189000  | 325000  | NULL     | 450000
riverview     | 250000  | 300000  | 350000  | NULL</code>

Each row represents a neighborhood, and each column represents a bedroom count. The values represent the average price for that specific neighborhood and bedroom combination. NULL indicates no listings were found for that particular combination. This provides a clear and concise summary of average housing prices. Remember to adjust the bedroom categories in the unnest function if your data includes a different range of bedroom counts.

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