SQL
Calculate Total Sales Per Product Category with Minimum Threshold
Summarize total revenue for each product category, filtering to show only categories that have achieved a minimum sales volume, using GROUP BY and HAVING.
SELECT
pc.category_name,
SUM(oi.quantity * oi.price_at_order) AS total_sales
FROM product_categories pc
JOIN products p ON pc.category_id = p.category_id
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY pc.category_name
HAVING SUM(oi.quantity * oi.price_at_order) > 10000
ORDER BY total_sales DESC;
How it works: This query calculates the total sales for each product category. It joins `product_categories`, `products`, and `order_items` to link sales data to categories. The `GROUP BY pc.category_name` clause aggregates the sales figures for each unique category. The `SUM(oi.quantity * oi.price_at_order)` calculates the total sales for each group. Crucially, the `HAVING` clause filters these aggregated results, displaying only those categories whose total sales exceed $10,000, providing insights into top-performing categories. Results are ordered by `total_sales` in descending order.