SQL
Advanced SQL Data Aggregation with GROUP BY and HAVING
Perform powerful data aggregation in SQL using GROUP BY to summarize data and HAVING to filter these aggregated results based on specific conditions.
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
ORDER BY average_price DESC;
How it works: This query aggregates product data by `category_id`. `COUNT(product_id)` calculates the total number of products, and `AVG(price)` computes the average price for each category. The `GROUP BY` clause groups rows that have the same `category_id`. The `HAVING` clause then filters these *grouped* results, only showing categories with more than 5 products and an average price above 50.00. `HAVING` is similar to `WHERE` but operates on aggregated results.