The HAVING clause filters grouped rows produced by GROUP BY. While WHERE filters individual rows before grouping, HAVING filters groups after aggregation.
GROUP BY to aggregate rows into groups.SUM(), AVG(), COUNT(), MAX(), MIN().WHERE filters rows before aggregation; HAVING filters after aggregation.HAVING comes after GROUP BY (and after ORDER BY if present).
-- Syntax for HAVING: filter grouped results using aggregate conditions
SELECT column1, AGGREGATE_FUNCTION(column2)
FROM table
GROUP BY column1
HAVING condition;
-- Get departments with an average salary greater than 50000
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;
-- Get products with total sales greater than 1000
SELECT product_name, SUM(sales)
FROM products
GROUP BY product_name
HAVING SUM(sales) > 1000;
-- Example: combining WHERE and HAVING: filter rows then filter groups
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING SUM(amount) > 5000;
1. Filtering rows: Any WHERE clause is applied first to reduce rows.
2. Grouping: Rows are grouped by the columns in GROUP BY.
3. Aggregation: Aggregate functions compute values per group (e.g., SUM, AVG).
4. Filtering groups: HAVING evaluates conditions against those aggregated values and keeps groups that satisfy them.
Example explanation: the query HAVING AVG(salary) > 50000 keeps only departments whose average salary exceeds 50000.
HAVING only when you need to filter by aggregated values.WHERE to filter rows before aggregation for performance reasons.GROUP BY to avoid errors and ensure predictable results.WHERE and use HAVING sparingly.HAVING.HAVING (e.g., HAVING COUNT(*) > 5 AND AVG(rating) > 4).