SQL practice › SQL errors › Column must appear in GROUP BY
Column must appear in the GROUP BY clause or be used in an aggregate function
The error means the query has a GROUP BY (or an aggregate such as COUNT) and the SELECT list also contains a column that is neither one of the grouped columns nor inside an aggregate function. Each group is many rows folded into one, so that column has many values per group, and the database has no rule for choosing one. Oracle calls it "not a GROUP BY expression".
There are three fixes, and they answer three different questions: drop the column, aggregate it, or stop grouping and filter rows instead. Which one is right depends on what you wanted to know.
One warning before the live queries. The database on this page is SQLite, and SQLite does not raise this error. It runs the query and fills the column with a value from one row of each group. So below you will see what the other databases are protecting you from.
The table
Nine employees in three departments: four in Engineering, three in Sales, two in Support.
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| department | name | COUNT(*) |
|---|---|---|
| Engineering | Raj | 4 |
| Sales | Maya | 3 |
| Support | Nora | 2 |
What the message looks like in each database
- Oracle:
ORA-00979: not a GROUP BY expressionin 19c and 21c. In Oracle Database 26ai the same code readsmust appear in the GROUP BY clause or be used in an aggregate function, after the name of the expression. - SQL Server: error 8120,
Column 'employees.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. - MySQL: error 1055,
Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mydb.t.address' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by(the example in its manual). MySQL checks this because theONLY_FULL_GROUP_BYmode is on by default. With the mode off there is no error, and the manual says the server is then free to choose any value from each group. - PostgreSQL: its manual states the rule rather than the message. A SELECT list may not refer to an ungrouped column unless the column is inside an aggregate function or is functionally dependent on the grouped columns.
- SQLite: no error. Its documentation calls such a column a "bare column", says the value comes from an arbitrary row of the group, and notes that most other database engines raise an error instead.
The same thing happens with an aggregate and no GROUP BY at all, as in SELECT name, AVG(salary) FROM employees. Oracle answers ORA-00937: not a single-group group function and MySQL error 1140, In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column ….
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| name | AVG(salary) |
|---|---|
| Maya | 69888.89 |
GROUP BY turns the whole table into one group, so name is a column with nine values and room for one. The average is right. The name beside it means nothing.The version that looks right
The dangerous case is the one where the silent answer looks like the answer you wanted.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| department | name | MAX(salary) |
|---|---|---|
| Engineering | Raj | 95000 |
| Sales | Maya | 72000 |
| Support | Nora | 46000 |
MIN or MAX, the other columns are taken from the row that holds that minimum or maximum. SQLite's documentation says it knows of no other database that does this, and it stops working on a tie. Change MAX to MIN: Omar and Lena both earn the lowest Sales salary, and the result has room for one name.Fix 1: adding the column to GROUP BY (usually not what you want)
The message suggests it, and it does make the error go away everywhere. It also changes the question.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| department | name | COUNT(*) |
|---|---|---|
| Engineering | Ivan | 1 |
| Engineering | Kim | 1 |
| Engineering | Raj | 1 |
| Engineering | Zoe | 1 |
| Sales | Lena | 1 |
| Sales | Maya | 1 |
| Sales | Omar | 1 |
| Support | Nora | 1 |
| Support | Theo | 1 |
GROUP BY only when you really want one row for each of its values. If the question was "how many people per department", the fix is to take name out of the SELECT list.Fix 2: aggregate the column
If you want one value per group from that column, say which one: the smallest, the largest, how many.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| department | COUNT(*) | MAX(salary) | MIN(hire_date) |
|---|---|---|---|
| Engineering | 4 | 95000 | 2018-09-03 |
| Sales | 3 | 72000 | 2019-03-11 |
| Support | 2 | 46000 | 2020-07-15 |
department is grouped, the other three columns are aggregates, so each has exactly one value per group. Each aggregate is computed on its own. The top salary and the earliest hire date in a row are not promised to belong to the same person.Fix 3: you wanted a row, not a group
"Who is the best-paid person in each department" asks for people, and a person is a row. Don't group: keep the rows whose salary equals the top salary of their own department.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| department | name | salary |
|---|---|---|
| Sales | Maya | 72000 |
| Engineering | Raj | 95000 |
| Support | Nora | 46000 |
Practice: 4 exercises
Each task comes from a query with a column that is neither grouped nor aggregated. SQLite would run the broken version, so the check compares your result with the right one, on this table and again on changed data. Column names don't matter; the columns and rows do.
SELECT department, name, COUNT(*) FROM employees GROUP BY department is refused by most databases. The question was only how many people work in each department. Write the valid query: the department and its number of employees.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
Show the answer
SELECT department, COUNT(*)
FROM employees
GROUP BY department| department | COUNT(*) |
|---|---|
| Engineering | 4 |
| Sales | 3 |
| Support | 2 |
The name was never part of the question. Without it, every selected column is either the grouped one or an aggregate: four people in Engineering, three in Sales, two in Support.
SELECT department, MIN(salary), hire_date FROM employees GROUP BY department has one column too many outside an aggregate. For each department, return the department, its lowest salary and the date of its most recent hire.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
Show the answer
SELECT department, MIN(salary), MAX(hire_date)
FROM employees
GROUP BY department| department | MIN(salary) | MAX(hire_date) |
|---|---|---|
| Engineering | 81000 | 2022-08-22 |
| Sales | 58000 | 2022-02-14 |
| Support | 43000 | 2023-04-03 |
The most recent hire is the largest date, so hire_date goes inside MAX. With the bare column, SQLite returns the hire date of whoever has the lowest salary, which is a different question: in Engineering that is Ivan, hired in 2021, while the most recent hire is from 2022.
Return each department, each salary paid in it, and how many people earn that salary: one row for every department and salary.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
Show the answer
SELECT department, salary, COUNT(*)
FROM employees
GROUP BY department, salary| department | salary | COUNT(*) |
|---|---|---|
| Engineering | 81000 | 1 |
| Engineering | 88000 | 2 |
| Engineering | 95000 | 1 |
| Sales | 58000 | 2 |
| Sales | 72000 | 1 |
| Support | 43000 | 1 |
| Support | 46000 | 1 |
Here adding the column to GROUP BY is the right fix, because the question asks for one row per department and salary. Seven rows for nine people: two salaries are shared by two people each.
SELECT department, name, MIN(salary) FROM employees GROUP BY department tries to name the lowest-paid person of each department. Return the department, name and salary of the lowest-paid person in each department. If two people tie for the lowest salary, return both.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
Show the answer
SELECT department, name, salary
FROM employees e
WHERE salary = (
SELECT MIN(salary)
FROM employees
WHERE department = e.department
)| department | name | salary |
|---|---|---|
| Sales | Omar | 58000 |
| Sales | Lena | 58000 |
| Engineering | Ivan | 81000 |
| Support | Theo | 43000 |
Four rows for three departments: Omar and Lena share the lowest Sales salary. A grouped query has one row per department and has to leave one of them out. Comparing each row with the minimum of its own department keeps every person who earns it.
Questions people ask
What does "not a GROUP BY expression" (ORA-00979) mean?
It is Oracle's name for this error: an expression in the SELECT list, in ORDER BY or in HAVING is neither in the GROUP BY clause nor inside an aggregate function. Oracle's own advice is the first two fixes on this page: add the expression to GROUP BY, or wrap it in an aggregate to say which value of each group you want.
Why does my query run on SQLite or MySQL and fail on another database?
SQLite accepts a column that is neither grouped nor aggregated and takes its value from an arbitrary row of each group; its documentation presents that as an SQLite-specific extension. MySQL does the same when the ONLY_FULL_GROUP_BY mode is turned off. The query was never right. One database was returning an unpredictable value where the other returns an error.
Can I just add the column to GROUP BY?
Only if you want one row per value of that column. Adding it makes more, smaller groups, and totals and counts change with them: the live query above goes from three rows to nine. If the column was only there to be shown, aggregate it with MIN or MAX, or leave it out.
Does the rule apply to ORDER BY and HAVING too?
Yes. Oracle's description of ORA-00979 names the SELECT list, ORDER BY and HAVING, and MySQL's manual lists the same three places. After grouping, only the grouped columns and aggregates exist, so ORDER BY salary in a query grouped by department has the same problem as selecting salary.
I grouped by the primary key. Do I still have to list every column?
Not everywhere. PostgreSQL's manual says other columns of a table may be selected when the table's primary key is grouped, because each group then has one row of that table and one value of each of its columns. MySQL's manual describes the same detection for a primary key or a unique NOT NULL column. Listing the columns in GROUP BY anyway works on every database.