GROUP BY and HAVING: The Misconceptions That Cause Wrong Answers

Concept in Simple Words: GROUP BY collapses multiple rows with the same value into single summary rows. The WHERE clause filters individual rows BEFORE they are grouped. The HAVING clause filters grouped summaries AFTER the grouping has occurred. You cannot reference aggregates in the WHERE clause.

Deep Walkthrough & Code: Let's look at a query designed to find departments with more than 5 employees making over 50k:

SELECT dept_id, COUNT(*)
FROM employees
WHERE salary > 50000
GROUP BY dept_id
HAVING COUNT(*) > 5;

Step-by-Step Dry Run: Order of Execution is critical:
- FROM employees: Load all employees.
- WHERE salary > 50000: Remove employees making <= 50k.
- GROUP BY dept_id: Group the remaining employees by department ID.
- COUNT(*): Count employees in each department group.
- HAVING COUNT(*) > 5: Remove department groups with 5 or fewer employees.
- SELECT: Return the resulting department IDs and headcounts.

Production Level Issue & Fix: Putting non-aggregate filters in the HAVING clause is a common production mistake (e.g. `HAVING dept_id = 3`). This forces the database to group all rows first and filter afterwards. In production, always place non-aggregate filters in the WHERE clause to minimize the row count before grouping, speeding up execution.