Answer

WHERE filters rows before grouping and aggregations are applied, meaning it determines which records will be included in the grouping/aggregation. HAVING filters groups after aggregate functions have been applied.

• WHERE: aggregate functions (SUM, AVG, etc.) cannot be used here because filtering happens BEFORE grouping.
• HAVING: aggregate functions can be used here because filtering occurs AFTER grouping.

Example:

-- Get departments with total salary greater than 50000, excluding resigned employees
SELECT department, SUM(salary) as total
FROM employees
WHERE status = 'active'
GROUP BY department
HAVING SUM(salary) > 50000;

Here:

• WHERE status = 'active' — removes resigned employees before calculating sums by department;
• HAVING SUM(salary) > 50000 — shows only those departments where the total salary exceeds 50000.

Trick Question

Can you use aggregate functions in WHERE?

Error: trying to write WHERE SUM(amount) > 100, which is not allowed — aggregate functions are ONLY used in HAVING.

Example (WRONG):

SELECT customer_id, SUM(amount)
FROM orders
WHERE SUM(amount) > 100
GROUP BY customer_id
-- ERROR: incorrect use of SUM() in WHERE

Correct:

SELECT customer_id, SUM(amount)
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 100;

Examples of Real Mistakes Due to Ignorance of the Topic

---

Story

CRM Development. Attempted to filter customers with more than 5 orders using WHERE COUNT(order_id) > 5. The query did not work. Result: the number of customers in the report was incorrect because COUNT cannot be used in WHERE.

---

Story

Business Analytics. Instead of filtering inactive products in WHERE, they used HAVING. Result: aggregation of "empty" groups and slow SQL. Fixed by moving the status filter to WHERE.

---

Story

Complex Report. Used HAVING without GROUP BY to filter individual rows. In some DBMS, this causes an error, while in others it produces unexpected behavior. Conclusion: HAVING must come after grouping and for aggregate conditions.