SQL
Aggregate Data and Filter Groups with SQL
Understand how to group rows with GROUP BY, calculate aggregate functions (SUM, AVG, COUNT), and filter groups using the HAVING clause in SQL.
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 groups products by their `category_id` and then calculates the total number of products and their average price within each category. The `HAVING` clause filters these aggregated groups, showing only categories that have more than 5 products and an average price greater than 50.00. This is essential for generating reports, statistics, and dashboard insights in web applications.