🚨 Postgres Error: 'must appear in the GROUP BY clause or be used in an aggregate function' — Cause, Fix, and Troubleshooting Guide

Overview

PostgreSQL raises this error when a query mixes an aggregate function (like count(*)) with a plain column that is neither listed in GROUP BY nor wrapped in an aggregate. SQL cannot decide which value of the ungrouped column to show alongside the aggregated result, so it rejects the query.

ERROR:  column "orders.status" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT status, count(*) FROM orders;

The rule: once a query contains an aggregate, every selected non-aggregated column must appear in GROUP BY. This is standard SQL, and Postgres enforces it strictly — with one useful exception noted below for primary keys.

Symptoms

• A SELECT combining a bare column with count, sum, avg, max, or min fails immediately at parse time.
• The LINE 1: pointer names the offending column.
• Adding one column to GROUP BY often reveals another column with the same problem.
• The query works in MySQL (which historically allowed this) but fails when ported to Postgres.

SELECT status, count(*) FROM orders;

ERROR:  column "orders.status" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT status, count(*) FROM orders;

Common Root Causes

1. A non-aggregated column selected alongside an aggregate

The classic case: you want counts per status but forgot to group by status.

SELECT status, count(*)
FROM orders;

Postgres has no single status to attach to the single count(*) — it needs one count per status.

2. Selecting extra columns not in GROUP BY

You group by one column but select others that are not grouped or aggregated.

SELECT customer_id, status, count(*)
FROM orders
GROUP BY customer_id;

status is neither grouped nor aggregated, so it triggers the error even though customer_id is fine.

3. Grouping semantics misunderstood

Every ungrouped selected column must be functionally reducible to one value per group. A raw column rarely is, unless it is functionally dependent on a grouped primary key — Postgres allows that specific case.

-- allowed: customers.name is functionally dependent on the grouped primary key
SELECT c.id, c.name, count(o.id)
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;

Because c.id is the primary key, Postgres knows c.name is uniquely determined and permits it without listing it.

How to diagnose

Step 1: Identify which columns are aggregated

Read the SELECT list and mark each item as either an aggregate call or a plain column.

SELECT status,          -- plain column
       priority,        -- plain column
       count(*)         -- aggregate
FROM orders
GROUP BY status;

Step 2: Compare plain columns against GROUP BY

Every plain column must appear in GROUP BY. Here priority is missing, which is the offender.

SELECT status, priority, count(*)
FROM orders
GROUP BY status;

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

Step 3: Confirm the grouping you actually want

Decide the grain of the result: one row per status, per status+priority, or per customer. That decision determines what belongs in GROUP BY.

SELECT status, priority, count(*)
FROM orders
GROUP BY status, priority
ORDER BY status, priority;

Fixes

Add the column to GROUP BY

If the column defines the grain of the result, group by it:

SELECT status, count(*)
FROM orders
GROUP BY status
ORDER BY count(*) DESC;

Wrap the column in an aggregate

If you only need one representative value per group, aggregate it:

SELECT customer_id,
       count(*)            AS order_count,
       max(created_at)     AS last_order,
       array_agg(status)   AS statuses
FROM orders
GROUP BY customer_id;

Group by the primary key when selecting its other columns

Selecting many columns from one table? Group by its primary key and Postgres accepts the functionally dependent columns:

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

Use DISTINCT ON instead of GROUP BY

When you want one row per key with specific column values (not aggregates), DISTINCT ON is cleaner:

SELECT DISTINCT ON (customer_id) customer_id, status, created_at
FROM orders
ORDER BY customer_id, created_at DESC;

Use a window function instead of grouping

If you need the aggregate alongside every detail row, a window function avoids collapsing rows entirely:

SELECT id, status, customer_id,
       count(*) OVER (PARTITION BY status) AS status_total
FROM orders;

What to watch out for

• Adding columns to GROUP BY changes the result grain — you get more, finer rows, which may not be the aggregation you intended.
• MySQL’s old lenient behavior returned an arbitrary value for ungrouped columns; Postgres refusing it is a feature, not a bug — do not fight it with hacks.
• The primary-key exception only works when the real primary key (or a UNIQUE NOT NULL key) is in GROUP BY; grouping by a non-key column will not enable it.
• Window functions and GROUP BY solve different problems: use windows to keep detail rows, grouping to collapse them.
• array_agg and string_agg accept ORDER BY inside the call — use it for deterministic output.

Related

• Postgres Error: ‘column does not exist’
• Postgres Error: ‘relation does not exist’
• Postgres Error: ‘canceling statement due to statement timeout’