Lesson 7 — Querying Data

GROUP BY and HAVING

GROUP BY collapses rows sharing a common value into summary groups, and HAVING filters those groups based on an aggregate condition — something WHERE cannot do.

Grouping Rows

GROUP BY produces one output row per unique value (or combination of values) in the grouped column(s), letting you calculate an aggregate for each group rather than the whole table.

T-SQL — GROUP BY 
SELECT Department, COUNT(*) AS EmployeeCount, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department;

Filtering Groups With HAVING

WHERE filters individual rows before grouping happens; HAVING filters the resulting groups afterward, based on an aggregate condition. This is why "HAVING COUNT(*) > 5" works, but "WHERE COUNT(*) > 5" does not.

T-SQL — HAVING 
SELECT Department, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 5;

Common Mistakes

  • Trying to use an aggregate function inside WHERE instead of HAVING, which SQL Server rejects.
  • Selecting a column that isn't in the GROUP BY clause and isn't wrapped in an aggregate function, causing an error.
  • Confusing the order of filtering: WHERE runs before grouping, HAVING runs after.
  • Grouping by a column that has many unique values (like an ID) when a broader category column was intended.

Professional Tip

A useful mental model: WHERE filters rows before they're grouped, HAVING filters the groups after aggregation. If your condition involves an aggregate function like COUNT or SUM, it belongs in HAVING.

Your Turn

Write a query that groups orders by customer, calculates each customer's total spend, and uses HAVING to show only customers who have spent more than R5000.

Mini Quiz

Which clause would you use to filter groups based on an aggregate condition, like COUNT(*) > 10?