Published: 2026-11-02

Why GROUP BY Works in MySQL and SQLite but Fails in PostgreSQL and SQL Server

A query that selects a column which is neither in GROUP BY nor inside an aggregate asks for one value per group from a group that may hold several. PostgreSQL (42803), SQL Server (Msg 8120) and Oracle (ORA-00979) refuse it. MySQL refuses it too since 5.7.5 (ERROR 1055), unless ONLY_FULL_GROUP_BY has been removed from sql_mode. SQLite runs it and returns a value from an arbitrary row. The exception is a column that is functionally dependent on the grouped columns, such as another column of a table whose primary key you grouped by: MySQL and PostgreSQL accept that, SQL Server and Oracle do not. We ran the same statements on all five databases on November 1, 2026. Below are the exact messages, the row SQLite and MySQL picked (and how an index changed it), ANY_VALUE() support per engine, and the fixes that run everywhere.

Results by database

Tested on November 1, 2026 on SQL Server 2025 CU8 (17.0.4075.5), PostgreSQL 16.4, MySQL 8.0.46 with its default sql_mode, Oracle AI Database 26ai Free (23.26.2) and SQLite 3.51.1. Each column is one of the statements in the test.

So “MySQL allows it” is not accurate: a MySQL 8.0 server with default settings rejects Q1 just as PostgreSQL does. When a query like Q1 runs on MySQL, either the column is functionally dependent on the grouped columns, or ONLY_FULL_GROUP_BY is off for that server or session. Check with SELECT @@SESSION.sql_mode;.

The test

Two tables: customers with a primary key, and orders that copy the customer name onto each order. Customer 10 was renamed between orders, so its two orders carry two different names. That is the case the error protects you from.

CREATE TABLE jam_gb1101_customers (customer_id INT NOT NULL PRIMARY KEY, name VARCHAR(50) NOT NULL);
CREATE TABLE jam_gb1101_orders (order_id INT NOT NULL PRIMARY KEY, customer_id INT NOT NULL,
                                customer_name VARCHAR(50) NOT NULL, amount DECIMAL(10,2) NOT NULL);
INSERT INTO jam_gb1101_customers (customer_id, name) VALUES (10, 'Acme Ltd');
INSERT INTO jam_gb1101_customers (customer_id, name) VALUES (20, 'Birch & Co');
INSERT INTO jam_gb1101_orders VALUES (1, 10, 'Acme Ltd', 120.00);
INSERT INTO jam_gb1101_orders VALUES (2, 20, 'Birch & Co', 50.00);
INSERT INTO jam_gb1101_orders VALUES (3, 10, 'Acme Limited', 80.00);
INSERT INTO jam_gb1101_orders VALUES (4, 20, 'Birch & Co', 75.00);

On Oracle we used VARCHAR2 and NUMBER(10); everything else ran unchanged. The statements:

-- Q1: a column that is neither grouped nor aggregated
SELECT customer_id, customer_name, SUM(amount) AS total FROM jam_gb1101_orders GROUP BY customer_id;

-- Q2: an aggregate next to a plain column, no GROUP BY at all
SELECT customer_name, SUM(amount) AS total FROM jam_gb1101_orders;

-- Q3: group by the customers primary key, select another customers column
SELECT c.customer_id, c.name, SUM(o.amount) AS total
FROM jam_gb1101_customers c JOIN jam_gb1101_orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id;

-- Q4: group by the orders column that the join makes equal to that key
SELECT o.customer_id, c.name, SUM(o.amount) AS total
FROM jam_gb1101_customers c JOIN jam_gb1101_orders o ON o.customer_id = c.customer_id
GROUP BY o.customer_id;

-- Q5: ANY_VALUE
SELECT customer_id, ANY_VALUE(customer_name) AS customer_name, SUM(amount) AS total
FROM jam_gb1101_orders GROUP BY customer_id;

The exact error messages

These are copied from the clients we used: sqlcmd, psql with VERBOSITY verbose, the mysql client and SQL*Plus.

SQL Server: Msg 8120

Msg 8120, Level 16, State 1, Line 1
Column 'jam_gb1101_orders.customer_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Q2 gives the same message, even though there is no GROUP BY clause in it. Q3 and Q4 give it for jam_gb1101_customers.name. Microsoft’s GROUP BY reference states the rule without exceptions: “you must include each table or view column in the GROUP BY list if you use it in any nonaggregate expression in the <select> list.” Our common SQL Server errors post ranks 8120 ninth among the errors that failed queries in Jam SQL Studio.

PostgreSQL: SQLSTATE 42803

ERROR:  42803: column "jam_gb1101_orders.customer_name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT customer_id, customer_name, SUM(amount) AS total FROM...
                            ^

42803 is grouping_error in PostgreSQL’s error code table. Without verbose mode psql prints the same line without the code. Q4 fails with column "c.name" must appear in the GROUP BY clause….

MySQL: ERROR 1055 and ERROR 1140

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'jam_gb1101.jam_gb1101_orders.customer_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'jam_gb1101.jam_gb1101_orders.customer_name'; this is incompatible with sql_mode=only_full_group_by

The first is Q1 and the second is Q2. Drivers report them by symbol: ER_WRONG_FIELD_WITH_GROUP for 1055 and ER_MIX_OF_GROUP_FUNC_AND_FIELDS for 1140. The default sql_mode of our 8.0.46 server starts with ONLY_FULL_GROUP_BY; the MySQL 5.7.5 release notes record the change that put it there, together with the detection of functional dependence.

Oracle: ORA-00979 and ORA-00937

SELECT customer_id, customer_name, SUM(amount) AS total FROM jam_gb1101_orders GROUP BY customer_id
                    *
ERROR at line 1:
ORA-00979: "CUSTOMER_NAME": must appear in the GROUP BY clause or be used in an aggregate function
Help: https://docs.oracle.com/error-help/db/ora-00979/

ORA-00937: not a single-group group function

Oracle 26ai printed this wording of ORA-00979, close to PostgreSQL’s. The classic text is ORA-00979: not a GROUP BY expression; Oracle’s error help page lists both. Q2 raises ORA-00937 instead (help page).

SQLite: no error

SQLite returned two rows for Q1, 10 | Acme Ltd | 200 and 20 | Birch & Co | 125, and one row for Q2: Acme Ltd | 325. The next section shows why Acme Ltd is not a dependable answer.

Which row SQLite and MySQL pick, and why you can’t rely on it

The SQLite manual’s section on bare columns in an aggregate query says such a column takes the value of one of the input rows of the group, and “you usually do not know which input row is used.” It calls bare columns “an SQLite-specific extension”. The MySQL manual says the same of MySQL without ONLY_FULL_GROUP_BY: “the server is free to choose any value from each group, so unless they are the same, the values chosen are nondeterministic”, and adding ORDER BY does not influence the choice.

We checked it on our data. With no index on customer_name, both engines answered Acme Ltd for customer 10. Then we added one index and ran the identical query again:

CREATE INDEX jam_gb1101_ix ON jam_gb1101_orders (customer_id, customer_name, amount);
SELECT customer_id, customer_name, SUM(amount) AS total FROM jam_gb1101_orders GROUP BY customer_id;

The data and the query did not change; the plan did, and with it the name returned for customer 10. The value comes from whichever row the plan reads first or last, and the plan changes when someone adds an index, when the table grows or when statistics change. The total, 200, was right both times; only the ungrouped column moved.

SQLite has one documented exception. When a query has exactly one min() or max(), bare columns take their values from the row that holds the minimum or maximum (since SQLite 3.7.11). In our test SELECT customer_id, customer_name, MAX(amount) … GROUP BY customer_id returned Acme Ltd (the 120.00 order) and the MIN(amount) version returned Acme Limited (the 80.00 order). With both min() and max() in one query the rule no longer applies. That behaviour is specific to SQLite; MySQL does not promise it.

Grouping by a primary key: functional dependency

Q3 groups by c.customer_id, the primary key of jam_gb1101_customers, and selects c.name. A primary key identifies one row, so c.name has exactly one value per group. MySQL and PostgreSQL know this and run Q3; SQL Server and Oracle do not and raise 8120 and ORA-00979.

• PostgreSQL recognises the dependency only through a primary key. Its SELECT reference: “A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column.” The rule arrived in PostgreSQL 9.1. That is why Q4 fails there: o.customer_id is not the primary key of customers, even though the join makes it equal to one.
• MySQL also accepts a unique NOT NULL column as the grouping key, per its GROUP BY handling page, and in our test it accepted Q4 as well, where the join’s ON condition makes o.customer_id equal to the key.
• SQL Server documents no such exception, and Oracle rejected Q3 too. On both, list every selected column in GROUP BY; grouping by c.customer_id, c.name returns the same groups because the name is unique per key.

A query written for PostgreSQL that leans on this rule fails when you move it to SQL Server or Oracle. Our QUALIFY and GROUP BY ALL post covers a newer shortcut, GROUP BY ALL, which groups by every non-aggregated column; of the databases tested there, only Oracle 26ai runs it.

ANY_VALUE() by database

ANY_VALUE(col) says in the query what SQLite and permissive MySQL do silently: return some value from the group. It suits columns that are the same in every row of the group but that the engine cannot prove are, such as a name copied onto each order.

Fixes that run on every database

Pick the fix by what the column means, not by what makes the error go away.

1. The column belongs in the grouping. If each name should get its own total, add it: GROUP BY customer_id, customer_name. On our data that returned three rows on all five databases, because customer 10 has two names. That is often the right answer.
2. The column is the same in every row of the group. Wrap it in MAX() or MIN(). That runs on all five databases and returns the same value on every run. On our data MAX(customer_name) returned Acme Ltd on all five. ANY_VALUE() states the intent better where it exists.
3. The column comes from a lookup table. Group by that table’s primary key and its columns (GROUP BY c.customer_id, c.name). This is the portable form of Q3; it ran on all five.
4. You want the value from a particular row, such as the latest name. No aggregate picks "the latest" by itself; rank the rows with a window function and keep the first:

SELECT customer_id, customer_name, total
FROM (
  SELECT customer_id, customer_name,
         SUM(amount) OVER (PARTITION BY customer_id) AS total,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_id DESC) AS rn
  FROM jam_gb1101_orders
) ranked
WHERE rn = 1;

It returned Acme Limited, the name on customer 10’s latest order, with the total of 200, on all five databases. Window functions need MySQL 8.0 or later and SQLite 3.25 or later. They also avoid the trap from the previous section: the ORDER BY inside OVER decides the row, not the plan.

We would not fix MySQL’s 1055 by removing ONLY_FULL_GROUP_BY from sql_mode. It makes the query run and hands the choice of value to the plan, which is what changed our answer after one index.

The related mistake: an aggregate in WHERE

The other common GROUP BY error is a condition on an aggregate in WHERE, which is evaluated before rows are grouped. We ran … WHERE SUM(amount) > 150 GROUP BY customer_id on each database:

SQL Server  Msg 147, Level 15: An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
PostgreSQL  ERROR:  42803: aggregate functions are not allowed in WHERE
MySQL       ERROR 1111 (HY000): Invalid use of group function
Oracle      ORA-00934: group function is not allowed here
SQLite      misuse of aggregate: SUM()

Note that PostgreSQL uses 42803 for this too. Move the condition to HAVING, which runs after grouping, and it works on all five:

SELECT customer_id, SUM(amount) AS total FROM jam_gb1101_orders GROUP BY customer_id HAVING SUM(amount) > 150;

GROUP BY tips in Jam SQL Studio

We count failed queries by engine and error code in Jam SQL Studio’s anonymous telemetry; no query text is collected. In the 30 days to October 31, 2026, the codes from this post appeared in these numbers of sessions:

• SQL Server 8120: 17 sessions
• PostgreSQL 42803: 7 sessions
• MySQL ER_WRONG_FIELD_WITH_GROUP (1055): 4 sessions, and ER_MIX_OF_GROUP_FUNC_AND_FIELDS (1140): 4 sessions
• Oracle ORA-00979 or ORA-00937: 2 sessions

These are small numbers from one app, not a survey. SQLite does not appear: it runs Q1 without an error, so nothing is counted.

Version 1.5.8 adds a tip for these errors. When a query in the Query Editor fails with one of them, the Messages tab shows, under the server’s message:

Tip: every selected column must be listed in GROUP BY or wrapped in an aggregate such as COUNT() or MAX(). To filter on an aggregate, use HAVING instead of WHERE.

The same tip covers the aggregate-in-WHERE errors above, including SQLite’s misuse of aggregate. The Query Editor guide lists the other error tips (ambiguous columns, names that already exist, objects in another database).

Quick Answers

Q: Why does PostgreSQL say a column must appear in the GROUP BY clause?

A: The SELECT list names a column that is neither in GROUP BY nor inside an aggregate, so a group of several rows could have several values for it. PostgreSQL raises SQLSTATE 42803 instead of picking one. Add the column to GROUP BY, wrap it in an aggregate such as MAX() or any_value() (PostgreSQL 16 and later), or group by the table's primary key, which makes the other columns of that table functionally dependent.

Q: Why does the same GROUP BY query work in MySQL?

A: Since MySQL 5.7.5, ONLY_FULL_GROUP_BY is on by default and MySQL rejects the query with error 1055, unless the column is functionally dependent on the grouped columns, for example when you group by a primary key. If the query runs, either the column is functionally dependent or ONLY_FULL_GROUP_BY was removed from sql_mode. Without it, MySQL returns a value from any row of the group.

Q: Which row does SQLite pick for a column that is not in GROUP BY?

A: An arbitrary one. The SQLite documentation says a bare column takes its value from one of the input rows of the group, and you usually do not know which. In our test, adding an index changed the answer for the same data. The exception is a query with exactly one min() or max(): then bare columns come from the row that holds the minimum or maximum.

Q: Does SQL Server have ANY_VALUE?

A: Not in SQL Server 2025. Microsoft documents ANY_VALUE only for Warehouse and the SQL analytics endpoint in Microsoft Fabric, and on SQL Server 2025 CU8 it fails with Msg 195, not a recognized built-in function name. Use MIN() or MAX(), or add the column to GROUP BY.

Q: Can I group by the primary key and select other columns?

A: In MySQL and PostgreSQL, yes: both treat the other columns of a table as functionally dependent on its primary key. SQL Server and Oracle do not, and raise error 8120 and ORA-00979; list every selected column in GROUP BY there. SQLite accepts the query either way.

Q: Should I turn off ONLY_FULL_GROUP_BY?

A: Not to make an error go away. With it off, MySQL picks a value from any row of each group, and in our test the value changed after an index was added. Fix the query with GROUP BY, an aggregate or ANY_VALUE() instead, so the result means the same thing on every run.

Sources and method

Every statement ran on November 1, 2026 against our test servers: SQL Server 2025 CU8 Developer (17.0.4075.5) through sqlcmd, PostgreSQL 16.4 through psql, MySQL 8.0.46 through the mysql client, Oracle AI Database 26ai Free 23.26.2 through SQL*Plus, and SQLite 3.51.1 through better-sqlite3, the driver Jam SQL Studio uses. The tables were created for the test and dropped afterwards. Documentation quotes link to their source where they appear. Telemetry counts are sessions with an anonymous feature:query_error event carrying the error code, in the 30 days to October 31, 2026.

Summary

The rule is the same everywhere: one value per group, so a selected column must be grouped, aggregated or functionally dependent on the grouping. PostgreSQL, SQL Server and Oracle enforce it, and MySQL 8.0 enforces it with its default settings. They differ in what counts as dependent: a primary key in PostgreSQL, a key or a join equality in MySQL, nothing in SQL Server and Oracle. SQLite and MySQL without ONLY_FULL_GROUP_BY run the query and return a value from an arbitrary row, which in our test changed when we added an index. Add the column to GROUP BY, use MAX() or ANY_VALUE(), or rank rows with a window function when you need a particular one.

Last verified against learn.microsoft.com, postgresql.org, dev.mysql.com, docs.oracle.com, sqlite.org and mariadb.com documentation, and by running every statement on the five databases listed above, on 2026-11-01.