SQL
Summarizing Data with GROUP BY and Conditional Filtering with HAVING
Master SQL's GROUP BY to aggregate data and HAVING to filter those aggregated results, perfect for generating reports like total sales per category.
SELECT
c.category_name,
COUNT(p.product_id) AS total_products,
SUM(p.price * oi.quantity) AS total_revenue
FROM categories c
JOIN products p ON c.category_id = p.category_id
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY c.category_name
HAVING SUM(p.price * oi.quantity) > 10000;
How it works: This query aggregates data to provide a summary of products and revenue per category. It joins `categories`, `products`, and `order_items` tables. `GROUP BY c.category_name` groups all rows with the same category name together. `COUNT()` and `SUM()` are aggregate functions applied to these groups. The `HAVING` clause then filters these aggregated groups, showing only categories where the total revenue exceeds 10,000, allowing for conditional filtering on aggregated results.