Course contentsShow
← Data Science
Lesson 917 of 2,14523. SQL Aggregation and GroupingFree lesson

Common Patterns and Anti-Patterns

Identifying when each clause is required versus optional, and avoiding misuse in queries.

Common Patterns and Anti-Patterns

You'll learn to recognize best practices and common mistakes when combining WHERE, HAVING, and GROUP BY in SQL queries.

What Are Patterns and Anti-Patterns?

Think of patterns as recipes that consistently work well, while anti-patterns are cooking mistakes that seem right but ruin the dish. In SQL aggregation, certain combinations of clauses are elegant and efficient, while others create errors or performance problems.

Common Patterns (Do This!)

Pattern 1: Filter First, Then Aggregate Use WHERE to eliminate unnecessary rows before grouping. This reduces the data volume GROUP BY must process.

Pattern 2: Aggregate, Then Filter Aggregates Use HAVING to filter after aggregation when you need to test the result of aggregate functions like COUNT or SUM.

Pattern 3: Combine Both Strategically Use WHERE for row-level filters and HAVING for aggregate filters in the same query—they serve different purposes and work together.

Common Anti-Patterns (Avoid These!)

Anti-Pattern 1: Using WHERE with Aggregates You cannot write WHERE COUNT(*) > 5—this causes an error because WHERE runs before aggregation exists. Always use HAVING for aggregate conditions.

Anti-Pattern 2: Using HAVING for Non-Aggregate Filters Writing HAVING customer_name = 'Smith' works but is inefficient. Use WHERE instead—it filters earlier in the query execution.

Anti-Pattern 3: Forgetting Required Clauses Selecting non-aggregated columns without including them in GROUP BY causes errors in most databases.

Anti-Pattern 4: Over-Filtering with HAVING Alone Using only HAVING when WHERE would eliminate rows earlier wastes processing power on data you'll discard anyway.

Key Takeaway: Use WHERE to filter raw rows before grouping, HAVING to filter aggregated results after grouping, and always include non-aggregated SELECT columns in your GROUP BY—this combination maximizes both correctness and performance.