must appear in the GROUP BY clause in PostgreSQL, measured

ERROR: column "orders.total" must appear in the GROUP BY clause or be used in an aggregate function (SQLSTATE 42803, condition name grouping_error) means your query collapses rows into groups, but selects a column that can have more than one value within a group. PostgreSQL will not pick one for you. The cause is a column added to the SELECT list of a query with GROUP BY (or with count(*), sum(…) and no GROUP BY at all) that is neither grouped nor wrapped in an aggregate.

We measured every query below on PostgreSQL 17.11, 16.15, 15.19 and 12.22. The messages were identical on all four; only any_value() differs between versions.

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

select user_id, total, count(*) from orders group by user_id;
-- ERROR:  42803: column "orders.total" must appear in the GROUP BY clause
--         or be used in an aggregate function
-- LINE 1: select user_id, total, count(*) from orders group by user_id...
--                         ^

The message names the column with its table, or with its alias (column "o.total"), and LINE points at the first offending column. There is no DETAIL or HINT. An aggregate without GROUP BY fails the same way: select user_id, count(*) from orders complained about orders.user_id. Inside a transaction, the next statement got current transaction is aborted (25P02), the trap in current transaction is aborted. The fix: group by the column, aggregate it, or pick one row per group differently.

Why does GROUP BY id work for some columns and not others?

Because PostgreSQL knows that a table’s primary key determines the rest of that row. The documentation calls this functional dependency: “A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column.” We measured where that does and does not hold:

A unique, non-null column does not count; the documentation says PostgreSQL recognises functional dependency “only when a table’s primary key is included in the GROUP BY list”. In a join, group by the primary key of the table whose columns you show, not by the foreign key that equals it. Subqueries, CTEs and views lose the key altogether, and so does an expression on the key (group by u.id + 0): group by every column you select from them. A LEFT JOIN keeps it: grouped by u.id, u.email worked, including for a user with no orders.

What happens to a view that relies on the primary key?

PostgreSQL records the dependency. We created v_user_totals as select u.id, u.email, sum(o.total) … group by u.id, and then tried to drop the primary key:

alter table users drop constraint users_pkey;
-- ERROR:  2BP01: cannot drop constraint users_pkey on table users
--         because other objects depend on it
-- DETAIL:  view v_user_totals depends on constraint users_pkey on table users
-- HINT:  Use DROP ... CASCADE to drop the dependent objects too.

(We first dropped the foreign key from orders, which also depended on it.) With CASCADE the view was dropped, announced only by a NOTICE. What else blocks a drop, and what CASCADE takes with it, is in cannot drop because other objects depend on it. A migration that changes a primary key, say from id to (tenant_id, id), runs into this; more on such migrations in schema migrations on a shared table.

How do you get the latest row per group?

Not with GROUP BY. Use DISTINCT ON with a matching ORDER BY:

select distinct on (user_id) user_id, id, total, created_at
from orders
order by user_id, created_at desc;
--  user_id | id | total |       created_at
--        1 | 11 |    35 | 2026-10-05 00:00:00+00
--        2 | 13 |    40 | 2026-10-09 00:00:00+00

The ORDER BY has to start with the DISTINCT ON expressions; order by created_at desc alone gave SELECT DISTINCT ON expressions must match initial ORDER BY expressions (42P10). Without any ORDER BY the query ran and returned the oldest order for user 1 and the newest for user 2: the row that happened to come first, after an earlier no-op UPDATE had moved order 12 to the end of the table. The documentation says the “first row” of each set “is unpredictable unless ORDER BY is used”.

Can I just take any value, like any_value()?

On PostgreSQL 16 and 17, yes: any_value(total) is an aggregate, so the error goes away. On 15 and 12 it does not exist:

select user_id, any_value(total) from orders group by user_id;
-- 15.19, 12.22:
-- ERROR:  42883: function any_value(numeric) does not exist
-- HINT:  No function matches the given name and argument types.
--        You might need to add explicit type casts.

The documentation describes it as: “Returns an arbitrary value from the non-null input values”. With orders 12, 13 and 14 for one user, any_value(id) returned 12; after update o2 set total = total where id = 12, which changed nothing, it returned 13 on 16 and 17, because the update wrote a new row version at the end of the table. Use it only for a column that is equal within the group but not provably so; for “the latest”, use DISTINCT ON.

Can I GROUP BY an alias or a column number?

Yes, both: group by 1 and group by uid (for user_id as uid) gave the same result, and so did group by month for date_trunc('month', created_at) as month. The trap is an alias with the same name as a real column:

-- names: 'Ann', 'bob', 'ANN'
select lower(name) as name, count(*) from users group by name order by 1;
--  ann | 1
--  ann | 1
--  bob | 1
select lower(name) as name, count(*) from users group by 1 order by 1;
--  ann | 2
--  bob | 1

No error, but the first query grouped by the column, so ‘Ann’ and ‘ANN’ stayed apart. The documentation: “In case of ambiguity, a GROUP BY name will be interpreted as an input-column name rather than an output column name.” Use the position, a different alias, or repeat the expression. Aliases in WHERE and HAVING are a different story, covered in column does not exist.

Does ORDER BY or HAVING give the same error?

Yes: order by created_at and having total > 10, grouped by user_id, both gave 42803. order by max(created_at) desc and having max(total) > 30 worked.

How do you show group totals next to every row?

With a window function, which counts per group without collapsing the rows:

select id, user_id, total,
       count(*)   over (partition by user_id) as orders_per_user,
       sum(total) over (partition by user_id) as user_total
from orders order by user_id, id;
--  10 | 1 | 20 | 2 | 55
--  11 | 1 | 35 | 2 | 55
--  12 | 2 | 15 | 3 | 60  (and two more rows for user 2)

What do ORMs do with this error?

SQLAlchemy 2.1.6 sends what you write: grouping by Order.user_id while selecting User.email raised ProgrammingError wrapping psycopg2.errors.GroupingError (pgcode 42803). group_by(User.id) worked, also for select(User, func.count(Order.id)), whose columns the primary-key rule covers.

Django 6.1.2 does not raise this error; it changes the grouping instead. A field in order_by() that is not in values() is added to the GROUP BY, as the Django documentation describes: Order.objects.values("user_id").annotate(n=Count("id")).order_by("-created_at") generated GROUP BY 1, "dj_orders"."created_at" and returned five rows of n = 1. Order by the grouped field or by an aggregate instead (.annotate(last=Max("created_at")).order_by("-last") gave two groups, counts 2 and 3), or clear the ordering with .order_by(). annotate() before values() does the same thing: it grouped by the primary key and returned five rows of n = 1. If a Django aggregate returns too many rows, print qs.query. Other SQLSTATEs and their measured causes are in PostgreSQL error codes.

What we did not measure

Functional dependency through GROUPING SETS, ROLLUP or CUBE. Whether DISTINCT ON or a window function with row_number() is faster for “latest per group” on a large table. Other ORMs (Prisma, TypeORM, ActiveRecord, Hibernate), and Django queries other than the two above. PostgreSQL 13, 14 and 18.

Sources: PostgreSQL 17 documentation on SELECT (GROUP BY, DISTINCT, compatibility) and aggregate functions (any_value), read on 22 October 2026, and the Django documentation on aggregation (interaction with order_by()), read on 22 October 2026. All measurements are our own, on PostgreSQL 17.11, 16.15, 15.19 and 12.22 in containers, with SQLAlchemy 2.1.6, psycopg2 2.9.13 and Django 6.1.2, on 22 October 2026.