SQL
Aggregate Data and Filter Groups with GROUP BY and HAVING
Learn to summarize database records using SQL GROUP BY for counts, sums, and averages, then filter these aggregated results using the HAVING clause, ideal for reporting.
SELECT
category,
COUNT(product_id) AS total_products,
AVG(price) AS average_price
FROM
products
WHERE
status = 'active'
GROUP BY
category
HAVING
COUNT(product_id) > 5
ORDER BY
average_price DESC;
How it works: This code snippet demonstrates how to use `GROUP BY` to aggregate data and `HAVING` to filter those aggregated groups. It calculates the total number of active products and their average price for each category. The `HAVING` clause then filters these groups, showing only categories that have more than 5 products. This is highly useful for generating summarized reports or dashboard statistics in web applications.