SQL
Aggregate Data with GROUP BY and Filter with HAVING
Group rows based on common values to calculate sums, counts, or averages, and then filter these aggregated groups using the HAVING clause for insightful analysis.
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 AND AVG(price) > 50.00;
How it works: This query uses `GROUP BY` to aggregate data based on a common `category_id`, calculating the total number of products and their average price for each category. The `HAVING` clause then filters these aggregated groups, showing only categories that have more than 5 products and an average price greater than 50.00. `HAVING` is similar to `WHERE` but operates on grouped results, after the aggregation has occurred.