SQL
Aggregate and Filter Data with GROUP BY and HAVING
Master SQL aggregation using GROUP BY to summarize data, and apply the HAVING clause to filter groups based on aggregate conditions, perfect for reporting and analytics.
SELECT
category_id,
COUNT(product_id) AS total_products,
AVG(price) AS average_price
FROM
products
GROUP BY
category_id
HAVING
COUNT(product_id) > 5
ORDER BY
total_products DESC;
How it works: This query groups products by `category_id`, calculating the total number of products and their average price for each category. The `HAVING` clause then filters these grouped results, showing only categories with more than 5 products. This pattern is essential for generating summary reports and performing analytical queries based on aggregated data.