SQL
Group and Summarize Data with Aggregate Functions and HAVING
Master SQL aggregate functions (COUNT, SUM, AVG) with GROUP BY to summarize data and filter grouped results using the HAVING clause, perfect for reporting.
SELECT
category_id,
COUNT(product_id) AS total_products,
SUM(price * quantity) AS total_sales_value,
AVG(price) AS average_price_per_product
FROM order_items
GROUP BY category_id
HAVING COUNT(product_id) > 50
ORDER BY total_sales_value DESC;
How it works: This snippet groups `order_items` by `category_id` to calculate summary statistics: the total number of products, the sum of sales value, and the average price per product for each category. The `HAVING` clause then filters these grouped results, showing only categories with more than 50 products, which is useful for identifying significant categories.