Store Aggregated Sales Totals for Quick Reporting

Create a Materialized View with Aggregation

Write a PostgreSQL query to create a materialized view that aggregates total sales per product from the Sales table.

Solution:

-- Create a materialized view to calculate total sales for each product.
CREATE MATERIALIZED VIEW ProductSalesSummary AS
SELECT product_id, SUM(amount) AS total_sales
FROM Sales
GROUP BY product_id;

Explanation:

• Purpose of the Query:
• The goal is to precompute and store aggregated sales data for each product.
• This demonstrates how to use GROUP BY and aggregate functions within a materialized view.
• Key Components:
• SUM(amount) AS total_sales : Aggregates sales amounts for each product.
• GROUP BY product_id : Groups the data by product.
• Real-World Application:
• Ideal for dashboards and reporting systems that require quick access to summarized sales data.

Notes:

• Refreshing this view is necessary when new sales data is added.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to create a materialized view that aggregates the total quantity sold per product from the OrderItems table, including only products with more than 500 units sold.
• Write a PostgreSQL query to create a materialized view that calculates the average order value per customer from the Orders table, filtering for customers with over 10 orders.
• Write a PostgreSQL query to create a materialized view that groups sales data by region and computes the maximum sale value in each region from the Sales table.
• Write a PostgreSQL query to create a materialized view that aggregates daily website visits from the Traffic table, excluding days with less than 100 visits.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous PostgreSQL Exercise: Create a Simple Materialized View.
Next PostgreSQL Exercise: Create a Materialized View with a JOIN.