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.

Skip to the 4 exercises ↓

The table

Nine employees in three departments: four in Engineering, three in Sales, two in Support.

employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Live queryNo error on SQLite, and a name that means nothing
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 3 rows
departmentnameCOUNT(*)
EngineeringRaj4
SalesMaya3
SupportNora2

What the message looks like in each database

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 ….

Live queryNo GROUP BY at all: one row, one arbitrary name
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 1 row
nameAVG(salary)
Maya69888.89

The version that looks right

The dangerous case is the one where the silent answer looks like the answer you wanted.

Live query"The best-paid person of each department"
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 3 rows
departmentnameMAX(salary)
EngineeringRaj95000
SalesMaya72000
SupportNora46000

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.

Live queryGROUP BY department, name: nine groups of one
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 9 rows
departmentnameCOUNT(*)
EngineeringIvan1
EngineeringKim1
EngineeringRaj1
EngineeringZoe1
SalesLena1
SalesMaya1
SalesOmar1
SupportNora1
SupportTheo1

Fix 2: aggregate the column

If you want one value per group from that column, say which one: the smallest, the largest, how many.

Live queryEvery column grouped or aggregated
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 3 rows
departmentCOUNT(*)MAX(salary)MIN(hire_date)
Engineering4950002018-09-03
Sales3720002019-03-11
Support2460002020-07-15

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.

Live queryThe best-paid person of each department, properly
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 3 rows
departmentnamesalary
SalesMaya72000
EngineeringRaj95000
SupportNora46000

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.

Exercise 1Drop the column

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
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Show the answer
SELECT department, COUNT(*)
FROM employees
GROUP BY department
Result · 3 rows
departmentCOUNT(*)
Engineering4
Sales3
Support2

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.

Exercise 2Aggregate the column

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
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Show the answer
SELECT department, MIN(salary), MAX(hire_date)
FROM employees
GROUP BY department
Result · 3 rows
departmentMIN(salary)MAX(hire_date)
Engineering810002022-08-22
Sales580002022-02-14
Support430002023-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.

Exercise 3Group by both columns

Return each department, each salary paid in it, and how many people earn that salary: one row for every department and salary.

Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Show the answer
SELECT department, salary, COUNT(*)
FROM employees
GROUP BY department, salary
Result · 7 rows
departmentsalaryCOUNT(*)
Engineering810001
Engineering880002
Engineering950001
Sales580002
Sales720001
Support430001
Support460001

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.

Exercise 4A row, not a group

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
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Show the answer
SELECT department, name, salary
FROM employees e
WHERE salary = (
  SELECT MIN(salary)
  FROM employees
  WHERE department = e.department
)
Result · 4 rows
departmentnamesalary
SalesOmar58000
SalesLena58000
EngineeringIvan81000
SupportTheo43000

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.

Keep going

Lesson 2 is seven drills on COUNT, SUM, AVG, GROUP BY and HAVING on a table of orders: totals per region and per product, and the customers with at least two orders. It's free, like lessons 1, 3 and 4 and the level test.

Related

SQL GROUP BY, with live queriesSQL top N per group, with live queriesSQL WHERE vs HAVING, with live queriesSQL GROUP BY practiceSQL skills testSQL errors, with a query to fixSQL practice online

Written and tested by Nico, who builds SheetDrill (about). Every query on this page was run on SQLite, and every exercise checked against right and wrong answers, before the page was published. Other databases differ in places; where they do, the page says so. Checked against: SQLite documentation: SELECT (bare columns in an aggregate query), SQLite documentation: Quirks, Caveats, and Gotchas, PostgreSQL documentation: SELECT (GROUP BY clause), Oracle Database error messages: ORA-00979, Oracle Database error messages: ORA-00937, SQL Server documentation: Database Engine events and errors (8000 to 8999), MySQL reference manual: MySQL Handling of GROUP BY. Last checked November 2026.