PostgreSQL Error 42803

SQLSTATE

42803

Condition name

grouping_error

Product

PostgreSQL

Versions

PostgreSQL 13 to 18 (examples run on PostgreSQL 18.6 in PGlite)

Category

Types & Expressions

Last reviewed

24 October 2026

What it means

SQLSTATE 42803 means a query mixes grouped rows with a column that is neither grouped nor aggregated, or uses an aggregate function in a place where aggregates are not allowed.

With GROUP BY, or with an aggregate such as count() or sum() and no GROUP BY, PostgreSQL returns one row per group. Every column in SELECT, HAVING and ORDER BY must then have a single value per group: either it is in the GROUP BY list, or it is inside an aggregate function. A plain column that can have several values in one group (several employee names in one department) has no single answer, so PostgreSQL raises 42803 (grouping_error) and names the column as table.column.

There is one exception: when you group by a table's primary key, PostgreSQL knows the table's other columns are functionally dependent on it and lets you select them without listing them. MySQL (with ONLY_FULL_GROUP_BY off) and older tools allow non-grouped columns and pick an arbitrary value; PostgreSQL never does. The same SQLSTATE covers an aggregate in WHERE (which is evaluated before grouping, so the condition belongs in HAVING), an aggregate in UPDATE ... SET, and one aggregate nested inside another.

Common causes

• SELECT lists a column that is not in GROUP BY, for example a name next to count(*) per department; common in queries ported from MySQL.
• SELECT mixes an aggregate with a plain column and has no GROUP BY at all, such as SELECT name, sum(salary) FROM emp.
• ORDER BY or HAVING refers to a column that is not grouped.
• A condition on an aggregate is written in WHERE instead of HAVING.
• The query tries to take the average of a count (avg(count(*))) in one step, which needs a subquery.

Diagnose it

Read-only These queries only read the system catalogs (pg_catalog, information_schema) and statistics views such as pg_stat_activity and pg_locks. They do not change data, roles or settings, so they are safe to run first.

SELECT dept, count(DISTINCT name) AS names_in_group
FROM emp
GROUP BY dept
HAVING count(DISTINCT name) > 1;

SELECT conname, pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'emp'::regclass
  AND contype = 'p';

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

SELECT dept,
       string_agg(name, ', ' ORDER BY name) AS names,
       count(*) AS employees
FROM emp
GROUP BY dept;

SELECT c.id, c.name, c.email, count(o.id) AS orders
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id;

SELECT DISTINCT ON (dept) dept, name, salary
FROM emp
ORDER BY dept, salary DESC;

SELECT dept, sum(salary) AS total
FROM emp
GROUP BY dept
HAVING sum(salary) > 5000;

SELECT avg(employees) AS avg_per_dept
FROM (SELECT dept, count(*) AS employees FROM emp GROUP BY dept) AS d;

Examples

A non-grouped column next to count(*)

CREATE TABLE emp (emp_id int PRIMARY KEY, name text, dept text, salary numeric(10,2));
INSERT INTO emp VALUES (1, 'Sara', 'Sales', 5000), (2, 'Ali', 'Sales', 4000), (3, 'Omar', 'IT', 6000);

SELECT dept, name, count(*)
FROM emp
GROUP BY dept;

ERROR:  column "emp.name" must appear in the GROUP BY clause or be used in an aggregate function

An aggregate in WHERE

CREATE TABLE emp (emp_id int PRIMARY KEY, name text, dept text, salary numeric(10,2));

SELECT dept
FROM emp
WHERE sum(salary) > 5000
GROUP BY dept;

ERROR:  aggregate functions are not allowed in WHERE

ORDER BY a column that is not grouped

CREATE TABLE emp (emp_id int PRIMARY KEY, name text, dept text, salary numeric(10,2));

SELECT dept, count(*)
FROM emp
GROUP BY dept
ORDER BY name;

ERROR:  column "emp.name" must appear in the GROUP BY clause or be used in an aggregate function

Average of a count in one step

CREATE TABLE emp (emp_id int PRIMARY KEY, name text, dept text, salary numeric(10,2));

SELECT avg(count(*)) FROM emp GROUP BY dept;

ERROR:  aggregate function calls cannot be nested

How to prevent it

Before writing a grouped query, decide what one output row represents (a department, a customer, a month) and make every other column either a grouping column or an aggregate. For "the whole row with the highest value per group", reach for DISTINCT ON or a window function rather than GROUP BY.

When moving queries from MySQL, test them on PostgreSQL early, or enable ONLY_FULL_GROUP_BY on MySQL so the same mistakes are rejected there too.

Common questions

Learn more on CodeWithSQL

• ORDER BY, GROUP BY and HAVING explained
• SQL aggregate functions explained
• Practice: count employees per department
• Practice: top earner per department

Official documentation

• PostgreSQL error codes (Appendix A)
• The GROUP BY and HAVING clauses
• Aggregate functions
• SELECT (DISTINCT ON)

Last technically reviewed 24 October 2026; examples and diagnostics run on PostgreSQL 18.6 (PGlite 0.5.11). SQL-level examples were run in PGlite (PostgreSQL compiled to WebAssembly); examples that need a running server are shown as documented in the PostgreSQL documentation.