Every query so far has returned individual rows. Aggregate functions do something different: they summarize many rows into a single value, how many employees are there, what's the average salary, what's the highest salary in each department. GROUP BY is what makes that last kind of question, one answer per group, possible.
Learning Objectives
- Use COUNT, SUM, AVG, MIN, and MAX to summarize a table.
- Group rows with GROUP BY to get one summary value per group instead of one for the whole table.
- Filter groups with HAVING, and explain why that's different from filtering rows with WHERE.
All Rows
Every employee
GROUP BY
Bucketed by department
Aggregate Applied
COUNT, AVG, etc. per bucket
One Row Per Group
Summary, not individual rows
The Core Aggregate Functions
Without GROUP BY, an aggregate function summarizes the entire table into a single row. These five functions and their behavior are identical across MySQL, PostgreSQL, SQLite, and SQL Server, standard SQL from the start.
| Function | Returns |
|---|---|
| COUNT(*) | Number of rows |
| SUM(column) | Total of a numeric column |
| AVG(column) | Average of a numeric column |
| MIN(column) | Smallest value in a column |
| MAX(column) | Largest value in a column |
SELECT COUNT(*) AS total_employees, AVG(salary) AS avg_salary, MAX(salary) AS top_salary FROM employees; -- Result: -- total_employees | avg_salary | top_salary -- 3 | 93333.33 | 98000
GROUP BY: One Summary Per Group
GROUP BY department buckets the rows by department first, then applies the aggregate functions separately within each bucket, one result row per distinct department instead of one row for the whole table.
SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_salary FROM employees GROUP BY department; -- Result: -- department | headcount | avg_salary -- Engineering | 2 | 96500 -- Research | 1 | 87000
Every column in SELECT that isn't inside an aggregate function must appear in GROUP BY. SELECT department, first_name, COUNT(*) FROM employees GROUP BY department is an error on most databases, first_name isn't aggregated and isn't grouped by, so the database can't know which first_name to show for a department with multiple employees.
Grouping by Multiple Columns
Grouping by more than one column creates a separate group for every unique combination, here, every department-and-status pairing gets its own count.
SELECT department, status, COUNT(*) AS headcount FROM employees GROUP BY department, status;
HAVING: Filtering Groups, Not Rows
WHERE salary > 90000 would filter out individual employees before grouping even happens, changing what gets averaged. HAVING AVG(salary) > 90000 runs after the groups and their averages are already computed, and keeps only the groups whose average passed the test.
- Filters individual rows, before any grouping happens
- Cannot reference an aggregate function like AVG(salary)
- Filters entire groups, after grouping and aggregating
- Built specifically to reference aggregate functions
SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department HAVING AVG(salary) > 90000;
Frequently Asked Questions
Can I use both WHERE and HAVING in the same query?
Yes, and it's common: WHERE filters individual rows before grouping (for example, excluding inactive employees), and HAVING then filters the resulting groups (for example, only departments with an average salary over some threshold). They operate at different stages and aren't interchangeable.
Does COUNT(column_name) count NULL values?
No, COUNT(*) counts every row regardless of NULLs, but COUNT(column_name) only counts rows where that specific column is NOT NULL, covered in full in the previous lesson on NULL.
Is there a function to combine text values from multiple rows in a group into one string?
Yes, but the name differs by database: GROUP_CONCAT() in MySQL and SQLite, STRING_AGG() in PostgreSQL and SQL Server. Both join a column's values from each group into one delimited string.
Common Beginner Mistakes
Putting a non-aggregated, non-grouped column in SELECT alongside GROUP BY
Most databases reject this outright as an error (MySQL in strict mode included). Every SELECT column must either be in GROUP BY or wrapped in an aggregate function.
Using WHERE to filter on an aggregate value
WHERE AVG(salary) > 90000 is a syntax error on every major database, WHERE runs before aggregation exists yet. HAVING is the clause built for filtering on aggregated values.
Forgetting that GROUP BY with no matching rows for a group simply omits that group, rather than showing zero
If no employees exist in a department, that department doesn't appear in the GROUP BY results at all, there's no row to produce a 0 count from. Getting an explicit "0 for every known department" requires a different technique (a LEFT JOIN against a full list of departments), beyond this lesson's scope.
Best Practices
- Use WHERE to filter rows before aggregation whenever possible, it's processed earlier and keeps unnecessary rows out of the aggregation entirely.
- Use HAVING only for conditions that genuinely depend on an aggregated value, not as a general-purpose substitute for WHERE.
- Double-check that every non-aggregated SELECT column is listed in GROUP BY, most databases will error if it isn't.
- Alias aggregate expressions (AS avg_salary) so the result columns are self-explanatory.
Interview Questions
Write a query showing each department's headcount, but only for departments with more than 5 employees.
SELECT department, COUNT(*) AS headcount FROM employees GROUP BY department HAVING COUNT(*) > 5; — GROUP BY creates the per-department counts, and HAVING filters down to the ones above the threshold.
Explain, step by step, the logical order WHERE, GROUP BY, and HAVING actually run in.
WHERE filters individual rows first. GROUP BY then buckets the remaining rows into groups and computes any aggregate functions. HAVING filters those resulting groups last. This order explains why WHERE can't reference an aggregate (it runs before they're computed) and HAVING can.
What's the difference between COUNT(DISTINCT department) and COUNT(department)?
COUNT(department) counts every non-NULL row. COUNT(DISTINCT department) counts only the unique department values, so if 50 employees exist across 4 departments, COUNT(department) returns 50 and COUNT(DISTINCT department) returns 4.
Summary
This lesson covered the core aggregate functions (COUNT, SUM, AVG, MIN, MAX), grouping rows into per-group summaries with GROUP BY, and filtering those groups with HAVING, as distinct from filtering individual rows with WHERE.
What's Next?
The next lesson covers joins, combining data that lives across more than one table.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.