Understanding the difference between WHERE and HAVING is crucial for writing correct GROUP BY queries.

The Key Difference

WHERE filters rows before grouping:

SELECT department, COUNT(*) as emp_count
FROM employees
WHERE salary > 50000  -- Filters before grouping
GROUP BY department;

HAVING filters groups after aggregation:

SELECT department, COUNT(*) as emp_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;  -- Filters after grouping

When to Use Each

• WHERE: Filter individual rows (use column names directly)
• HAVING: Filter aggregated results (use aggregate functions)

Combining Both

-- Find departments with avg salary > 75k among employees making > 40k
SELECT department, AVG(salary) as avg_salary
FROM employees
WHERE salary > 40000        -- Filter rows first
GROUP BY department
HAVING AVG(salary) > 75000; -- Filter groups second

Common Use Cases

Find High-Volume Customers

SELECT customer_id, COUNT(*) as order_count, SUM(total) as revenue
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY customer_id
HAVING COUNT(*) >= 5 OR SUM(total) >= 10000;

Identify Duplicate Records

SELECT email, COUNT(*) as duplicate_count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;