SQL
Calculate Aggregate Statistics with Grouping and Filtering
Learn how to group rows with GROUP BY and filter those groups using HAVING to calculate powerful aggregate statistics like sums, averages, and counts.
SELECT category_id, COUNT(*) AS total_products, AVG(price) AS average_price
FROM products
GROUP BY category_id
HAVING COUNT(*) > 5 AND AVG(price) > 50.00
ORDER BY average_price DESC;
How it works: This snippet demonstrates how to use `GROUP BY` to aggregate data based on a common column (`category_id`) and then apply a filter to the *groups* themselves using `HAVING`. It calculates the total number of products and their average price for categories that have more than 5 products and an average price above 50.00. `ORDER BY` sorts the results in descending order of average price.