What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
The short answer: GROUP BY sorts rows that share the same value into one group, and an aggregate function such as COUNT or AVG then calculates one result per group. The two filters play different roles. WHERE removes individual rows before any groups are formed. HAVING removes whole groups after the aggregate results have been calculated. Most SQL errors in this area come from placing a condition in the wrong one of those two clauses.
Putting a condition in the wrong place produces one of two outcomes: an error message, or a query that runs cleanly and returns numbers calculated over the wrong set of rows. The second outcome is harder to notice, so this guide covers both.
How GROUP BY forms groups
The examples in this guide use a small employees table. The data is illustrative and invented for teaching purposes.
| id | name | department | salary | active |
|---|---|---|---|---|
| 1 | Ana | Sales | 50000 | TRUE |
| 2 | Ben | Sales | 52000 | TRUE |
| 3 | Cara | Sales | 48000 | FALSE |
| 4 | Dev | IT | 70000 | TRUE |
| 5 | Eli | IT | 72000 | TRUE |
| 6 | Fay | IT | 68000 | TRUE |
| 7 | Gus | IT | 71000 | TRUE |
| 8 | Hana | IT | 69000 | TRUE |
| 9 | Ivan | HR | 45000 | TRUE |
Running SELECT department FROM employees GROUP BY department; returns one row for each distinct department: Sales, IT and HR. Each group is the set of source rows that share that department value. Everything an aggregate function reports is calculated from the rows inside one group, not from the table as a whole.
#1 Best Overall
What aggregate functions do
An aggregate function takes many input values and returns a single value. Inside a grouped query it runs separately for each group. The five functions most people meet first behave as follows.
| Function | Returns | Effect of NULL values |
|---|---|---|
COUNT(*) |
The number of rows in the group | Counts every row, including rows with NULL columns |
COUNT(column) |
The number of rows where column is not NULL |
NULL values are skipped |
SUM(column) |
The total of the non-NULL values | NULL values are skipped |
AVG(column) |
The mean of the non-NULL values | NULL values are skipped |
MIN(column) |
The smallest non-NULL value | NULL values are skipped |
MAX(column) |
The largest non-NULL value | NULL values are skipped |
Aggregates without GROUP BY
An aggregate can be used with no GROUP BY at all. PostgreSQL then treats all the selected rows as a single group, so the query returns one row. For example:
SELECT COUNT(*) AS total_employees, AVG(salary) AS avg_salary
FROM employees;On the sample table this returns one row with total_employees of 9 and an avg_salary of about 60,555.56 (the 545,000 total salary divided by 9).
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →The order in which SQL applies the clauses
The clauses in a SELECT are written in one order but are conceptually evaluated in another. This is the logical sequence that the PostgreSQL SELECT documentation describes, and it explains almost every WHERE vs HAVING rule:
- FROM assembles the input rows, including any joins.
- WHERE discards individual rows that do not meet its condition.
- GROUP BY forms groups from the rows that remain.
- Aggregate functions are calculated for each group.
- HAVING discards groups whose results do not meet its condition.
- SELECT produces the output columns, including any aliases.
- ORDER BY sorts the output.
This is a description of logic, not a promise about how an engine’s query plan executes. Its practical consequences are clear, though. An aggregate result does not exist at step 2, so it cannot be used in WHERE. An alias created in step 6 does not exist at steps 2 through 5, so it cannot be used in WHERE either.
An older PostgreSQL tutorial, written against version 7.3.4, put the difference this way:
The fundamental difference between
WHEREandHAVINGis this:WHEREselects input rows before groups and aggregates are computed (thus, it controls which rows go into the aggregate computation), whereasHAVINGselects group rows after groups and aggregates are computed.DriversOutdated Drivers Are Slowing You DownPerformancePC Slower Than It Used to Be?DriversCrashes, No Sound, or Screen Glitches?Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy.
That wording is historical. The current PostgreSQL SELECT reference is the authority for present-day behavior, and it describes the same split.
WHERE vs HAVING: three ways this goes wrong
Mistake 1: putting an aggregate in WHERE
This is the most common error. A learner wants departments whose average salary is above 60,000 and writes the condition in WHERE:
SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE AVG(salary) > 60000
GROUP BY department;PostgreSQL rejects this with ERROR: aggregate functions are not allowed in WHERE. The reason is the sequence above: when WHERE runs, no averages exist yet. The fix is to move the condition to HAVING, which runs after the averages are calculated:
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;On the sample table this returns one row: IT, 70000. Sales averages 50000 across all three of its employees, and HR averages 45000, so both groups are removed.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Clear out junk files and repair common Windows errors3Scan for outdated or missing drivers - takes under a minuteMistake 2: putting a row condition in HAVING
The reverse error is putting a condition on an individual row into HAVING. A learner who wants only active employees writes:
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING active = TRUE;PostgreSQL returns ERROR: column "employees.active" must appear in the GROUP BY clause or be used in an aggregate function. active differs from row to row inside each department, so it has no single value for the group. The fix is to filter the rows before grouping:
SELECT department, COUNT(*) AS employee_count
FROM employees
WHERE active = TRUE
GROUP BY department;When the condition is on a grouping column, such as HAVING department <> 'HR', the query is valid, because the department has one value per group. WHERE department <> 'HR' is still the better choice. It removes those rows before grouping, so the engine never builds the HR group, and the condition reads as what it is: a filter on source rows.
Mistake 3: the query runs and gives the wrong answer
The most dangerous version is the one that produces no error. Suppose the question is the average salary of active staff in each department. Leaving out the row filter changes the answer, and the query gives no warning:
Recommended Free Tools
| Department | Average salary, all staff (no filter) | Average salary, active staff (WHERE active = TRUE) |
|---|---|---|
| Sales | 50000 | 51000 |
| IT | 70000 | 70000 |
| HR | 45000 | 45000 |
Cara, the inactive Sales employee at 48000, is included in the first column and removed in the second. Both queries are valid SQL, so only a check against the question you meant to ask will catch the difference.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Choosing the right clause
| The condition refers to | Example | Clause |
|---|---|---|
| A column value on one source row | active = TRUE |
WHERE |
| An aggregate result | COUNT(*) >= 2, AVG(salary) > 60000 |
HAVING |
| A grouping column, one value per group | department <> 'HR' |
WHERE preferred; HAVING also valid |
If a condition can be written without referring to an aggregate, put it in WHERE. Use HAVING only when the condition needs the result of an aggregate.
A complete worked example
This query combines both clauses. It asks for each department with at least two active employees, showing the count and the average salary of those active employees:
SELECT department,
COUNT(*) AS employee_count,
AVG(salary) AS average_salary
FROM employees
WHERE active = TRUE
GROUP BY department
HAVING COUNT(*) >= 2;Walking through the clauses on the sample table:
WHERE active = TRUEremoves Cara, the only inactive row.GROUP BY departmentforms three groups: Sales (Ana, Ben), IT (five employees) and HR (Ivan).COUNT(*)andAVG(salary)are calculated for each group.HAVING COUNT(*) >= 2removes the HR group, which has only one active employee.
| department | employee_count | average_salary |
|---|---|---|
| Sales | 2 | 51000 |
| IT | 5 | 70000 |
Dialect differences that affect portable queries
The core WHERE and HAVING rules are the same across major engines. The details around them are not, and a query that works in one product may fail in another.
Free tools Windows power users keep installed
One-click scans. No signup required.
- PostgreSQL requires every selected column that is not inside an aggregate to appear in
GROUP BY. An aggregate used withoutGROUP BYproduces a single group, as shown above. - Microsoft SQL Server requires each nonaggregate column or view column in the SELECT list to be included in
GROUP BY. Violations produce error 8120:Column '...' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. - MySQL 8.4 allows some references to select-list expressions, including aliases, in
GROUP BYandHAVING. This is a product convenience, not standard SQL. With theONLY_FULL_GROUP_BYSQL mode enabled, non-grouped columns are rejected; that mode is part of MySQL’s default settings. - SQLite accepts a bare column that is neither grouped nor aggregated and returns a value from an arbitrary row in the group. The exception is a query with exactly one
MIN()orMAX(), where the bare columns come from the row holding that minimum or maximum. Do not rely on either behavior in code meant to run elsewhere.
For portable SQL, write the full expression in HAVING (for example HAVING COUNT(*) >= 2) rather than an alias, and group by every nonaggregated column you select.
Error messages and their fixes
| Message | Engine | Cause | Fix |
|---|---|---|---|
| aggregate functions are not allowed in WHERE | PostgreSQL | An aggregate such as AVG() appears in WHERE |
Move the condition to HAVING |
| must appear in the GROUP BY clause or be used in an aggregate function | PostgreSQL | A row-level column appears in HAVING or SELECT without grouping or aggregation | Filter the row in WHERE, or add the column to GROUP BY |
| Error 8120: Column is invalid in the select list | SQL Server | A nonaggregate SELECT column is missing from GROUP BY | Add the column to GROUP BY or wrap it in an aggregate |
When a grouped query returns surprising numbers without an error, check the WHERE clause first. Then confirm that each condition you intended to apply to a row is in WHERE, and that only conditions on aggregate results sit 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.
