SQL GROUP BY and HAVING

Medium

GROUP BY collapses rows sharing common values into summary rows; HAVING filters these groups after aggregation.

Why It Matters

GROUP BY is tested in virtually every SQL interview. Understanding the WHERE vs HAVING distinction and what can appear in SELECT alongside GROUP BY is critical.

How It Works

GROUP BY groups rows with identical values in specified columns into summary rows. Aggregate functions (COUNT, SUM, AVG, MIN, MAX) compute values across each group. Execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. WHERE filters individual rows BEFORE grouping. HAVING filters groups AFTER aggregation. Rule: every column in SELECT must either be in GROUP BY or inside an aggregate function.

Example

SELECT department, COUNT(*) as emp_count, AVG(salary) as avg_sal FROM employees WHERE status = 'active' GROUP BY department HAVING COUNT(*) > 5 ORDER BY avg_sal DESC;

Common Mistakes

Using WHERE with aggregate functions (WHERE COUNT(*) > 5 fails — use HAVING). Including a non-grouped, non-aggregated column in SELECT. Confusing when to use WHERE vs HAVING.

Test Your Understanding

Easy Q1

Which query correctly finds departments with more than 10 employees?

Hard Q2

SELECT dept, AVG(salary) FROM employees GROUP BY dept HAVING AVG(salary) > 50000 ORDER BY AVG(salary) DESC; Which keyword can be replaced with a column alias?

Hard Q3

Which query finds departments where ALL employees earn more than 50000?

Ready to Master SQL GROUP BY and HAVING?

Practice with adaptive questions. Track your misconceptions. Build real mastery.

Free account. No credit card required.

Related Concepts

SQL JOINs

SQL JOINs combine rows from two or more tables based on a related column.

SQL Subqueries

A subquery is a query nested inside another query, used to compute intermediate results for the outer query.

← All Sql Concepts Practice All Topics →