Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →Scan for outdated or missing drivers - takes under a minuteDriver Scan →GROUP BY collapses rows that share a value into one group, and aggregate functions such as COUNT, SUM, and AVG then calculate a summary for each group. The most common mistake in this area is putting a condition on an aggregate result into WHERE. WHERE filters individual rows before any grouping happens, so it cannot test a count or an average. That condition belongs in HAVING, which filters groups after the aggregates have been calculated.
What GROUP BY does
A table stores one row per record. Many questions, however, are about sets of rows: how many employees each department has, or total sales per region. GROUP BY answers these by gathering input rows that share the same value in one or more grouping expressions. Each distinct value becomes one group, and the query returns one output row per group.
As an Amazon Associate I earn from qualifying purchases.
Without GROUP BY, a query that uses an aggregate treats all of the selected rows as a single group. That is why SELECT COUNT(*) FROM orders; returns one number for the whole table.
Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Clear out junk files and repair common Windows errors3Fix the driver behind crashes, sound loss and screen glitchesAggregate functions
An aggregate function takes many input values and returns one value. The five you will meet first are shown below. Behavior described here follows the SQL standard and the major engines; verify dialect-specific details in your database’s documentation before relying on them.
#1 Best Overall
| Function | Returns | Typical use | NULL handling |
|---|---|---|---|
COUNT(*) |
Number of rows in the group | How many employees per department | Counts every row, including rows where columns are NULL |
COUNT(column) |
Number of non-NULL values in the column | How many employees have a manager assigned | Skips NULL values |
SUM(column) |
Total of the values | Total payroll per department | Skips NULL values |
AVG(column) |
Arithmetic mean of the non-NULL values | Average salary per department | Skips NULL values, so it is not the same as dividing SUM by COUNT(*) |
MIN(column) / MAX(column) |
Smallest or largest value | Earliest hire date, highest order total | Skips NULL values |
The NULL column matters more than it looks. If some salaries are missing, AVG(salary) averages only the rows that have a salary, while COUNT(*) still counts those employees. Decide which of these you actually want before you read a result as a headcount or a pay figure.
Reading a complete GROUP BY query
Consider this query:
SELECT department,
COUNT(*) AS employee_count,
AVG(salary) AS average_salary
FROM employees
WHERE active = TRUE
GROUP BY department
HAVING COUNT(*) >= 5;Each clause does one job:
- FROM employees names the input rows.
- WHERE active = TRUE removes individual rows that are not active. Those rows never enter a group.
- GROUP BY department creates one group per department among the remaining rows.
- COUNT(*) and AVG(salary) calculate a value for each group from only the rows that survived the WHERE step.
- HAVING COUNT(*) >= 5 discards any group whose count is below five. The aggregate is available here because it has already been computed.
- SELECT produces the output columns for the groups that remain.
The query therefore answers: among active employees, which departments have at least five people, and what is each one’s average salary?
The WHERE vs HAVING mistake
The mistake usually appears when someone wants to filter on a count or average and writes it in WHERE:
SELECT department, COUNT(*) AS employee_count
FROM employees
WHERE COUNT(*) >= 5
GROUP BY department;This fails. At the point where WHERE runs, no groups exist yet, so there is no count to test. PostgreSQL rejects the statement with the error aggregate functions are not allowed in WHERE, and SQL Server and other engines refuse it in a similar way. The fix is to move the condition to HAVING, as in the example above.
The reverse mistake is less likely to produce an error but still matters. A condition on a row-level value can be placed in HAVING when it references a grouped column, such as HAVING department <> 'HR'. The result is the same as putting it in WHERE, because a group is either kept or dropped as a whole. The difference is cost and meaning: the engine computes aggregates for every row first, then throws away whole groups. Put row-level conditions in WHERE so excluded rows never reach the aggregate calculation.
Which clause to use
| Condition is about | Example | Clause |
|---|---|---|
| An individual source row | active = TRUE, hire_date >= '2020-01-01' |
WHERE |
| An aggregate value | COUNT(*) >= 5, AVG(salary) > 80000 |
HAVING |
| A grouped column, applied to whole groups | department <> 'HR' |
WHERE preferred; HAVING gives the same rows but does more work |
The order of operations
For learning purposes, the logical sequence is:
- FROM builds the input rows.
- WHERE keeps only the rows that satisfy row-level conditions.
- GROUP BY forms groups, and aggregates are calculated for each one.
- HAVING keeps only the groups that satisfy group-level conditions.
- SELECT builds the output columns, and ORDER BY sorts the result.
This is the model PostgreSQL’s SELECT documentation describes. It explains what the result should be, not how the engine executes the query internally. Optimizers may reorder work as long as the result is the same.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Rules for selected columns
Every nonaggregated column in the SELECT list must be handled by the grouping. In the example above, department is in both SELECT and GROUP BY, so each output row has one department value. Selecting a column such as first_name without grouping it or wrapping it in an aggregate is invalid in strict SQL. Microsoft’s SQL Server documentation requires each nonaggregate column in the SELECT list to appear in GROUP BY.
Recommended Free Tools
MySQL has historically been more permissive, and its 8.4 Reference Manual describes the ONLY_FULL_GROUP_BY setting that enforces the strict rule. MySQL also allows some references to select-list aliases and expressions inside GROUP BY and HAVING. Those conveniences are not part of standard SQL and are not portable. Other engines, including PostgreSQL and SQL Server, do not accept select-list aliases in WHERE or HAVING, so write the full expression there.
Troubleshooting a grouped query
- An aggregate error appears in WHERE. Move the condition to HAVING, or wrap the query in a subquery if you need the aggregate in a row-level filter.
- A column is rejected as not grouped. Add it to GROUP BY, or apply an aggregate such as MIN or MAX if one value per group is what you want.
- Averages look too low or too high. Check for NULL values. AVG skips them, and COUNT(*) does not.
- A group count is unexpected. Check whether a WHERE condition ran before the grouping. Rows excluded there never contribute to any count.
- The same query works in one database and fails in another. Compare how each engine handles nonaggregate columns and select-list aliases in GROUP BY and HAVING.
The core rule holds across databases: conditions on source rows go in WHERE, and conditions on aggregate results go in HAVING.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
