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.

Go to:

• Optimizing Data with PostgreSQL Materialized Views Exercises Home. ↩
• PostgreSQL Exercises Home ↩

PREV : Create a Simple Materialized View.
NEXT : Create a Materialized View with a JOIN.

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