Column must appear in the GROUP BY clause: what it means

Three customers, eight orders and one question: how much each customer spent and what they bought. The first draft of the query looks reasonable:

SELECT customer_id, product, sum(amount) AS total
FROM orders
GROUP BY customer_id;

PostgreSQL 17 answers with this:

ERROR:  column "orders.product" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT customer_id, product, sum(amount) AS total FROM order...
                            ^

If you’re learning aggregation, a column that must appear in the GROUP BY clause is probably the first error you’ll hit. The caret points at product, and the message offers two ways out. The first one, adding the column to GROUP BY, makes the error go away.

That’s exactly why it’s dangerous.

What PostgreSQL is objecting to

GROUP BY turns many rows into one row per group. Customer 1 has three orders, so the result gets one row for them with one total field. The sum is no problem: three amounts added together make one number.

The product column is a different story.

Those three orders are a keyboard, a mouse and a second keyboard. The database has to put one product name into one field, and it doesn’t know which. It could pick any of them, but then the answer would depend on the order the rows happen to sit on disk, and you couldn’t reproduce it. PostgreSQL refuses instead.

With \set VERBOSITY verbose in psql, the same error also shows its code and where in the server’s source it was raised:

ERROR:  42803: column "orders.product" must appear in the GROUP BY clause or be used in an aggregate function
LOCATION:  check_ungrouped_columns_walker, parse_agg.c:1492

SQLSTATE 42803 is grouping_error. The server raises it while the query is being analysed, before the server reads a single row, so an empty table raises it just like a full one. I checked: a temporary table with no rows gives the same message.

ORDER BY follows the same rule. A query whose SELECT list holds only the customer and the sum, but which sorts by product, fails the same way, and this time the caret lands at the end of the line:

ERROR:  column "orders.product" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ... AS total FROM orders GROUP BY customer_id ORDER BY product;
                                                               ^

Sorting groups by product has the same problem as showing the product. Each group has several, so there’s nothing to sort on.

WHERE doesn’t know this rule, because it runs earlier. It filters individual rows before any group exists, so it can use any column of the table. A query that sums only the mice each customer bought runs fine and returns two customers at 90 each. The other direction doesn’t work. A sum only exists after grouping, so a condition on a sum in WHERE fails with the same SQLSTATE:

ERROR:  42803: aggregate functions are not allowed in WHERE
LINE 1: SELECT customer_id, sum(amount) FROM orders WHERE sum(amount...
                                                          ^

Both messages describe one sequence. WHERE picks rows, GROUP BY folds them into groups, and only then do SELECT, HAVING and ORDER BY see the grouped result. Once that order is in your head, you’ll rarely see either error.

So the message isn’t saying “you forgot a keyword”. It’s saying: this column has several values per group, and you asked for one.

How SQLite answers the same query

The error is easy to read as PostgreSQL being pedantic. SQLite shows what happens without it. The same query on the same data in SQLite 3.50.4 goes through without a word:

(1, 'keyboard', 590)
(2, 'monitor', 1125)
(3, 'mouse', 425)

Looks like an answer. Now insert the same eight orders in reverse order and run the same query:

(1, 'keyboard', 590)
(2, 'cable', 1125)
(3, 'cable', 425)

Customer 2 bought one monitor and one cable. SQLite filled product from one of the group’s rows, and which one depended on the order it scanned the table in. Changing the insert order changed the report. The sums are right; the product names mean nothing.

The reflex fix, and seven rows instead of three

The shortest path to a query without an error is to do what the first half of the message suggests:

SELECT customer_id, product, sum(amount) AS total
FROM orders
GROUP BY customer_id, product;

The result:

 customer_id | product  |  total
-------------+----------+---------
           1 | keyboard |  500.00
           1 | mouse    |   90.00
           2 | cable    |   25.00
           2 | monitor  | 1100.00
           3 | cable    |   25.00
           3 | headset  |  310.00
           3 | mouse    |   90.00
(7 rows)

The error is gone, and so is the question you asked. The group isn’t the customer any more; it’s the pair of customer and product. The query now totals each customer’s spending per product. Three rows became seven.

On a short list you’d spot that. It’s worse when the result feeds something else, like a filter. Say you want customers who spent more than 500 in total. Grouped by customer:

SELECT customer_id, sum(amount) AS total
FROM orders
GROUP BY customer_id
HAVING sum(amount) > 500;

 customer_id |  total
-------------+---------
           1 |  590.00
           2 | 1125.00
(2 rows)

The same filter on the reflex-fixed query:

 customer_id | product |  total
-------------+---------+---------
           2 | monitor | 1100.00
(1 row)

Customer 1, Anna, spent 590 and dropped off the list. She never spent more than 500 on any single product, and HAVING now checks the total per customer and product pair, not per customer. No error, no warning. Just a list that looks plausible and is wrong.

Monthly reports break the same way. Give the orders an ordered_at date and ask for sales per month. Grouping by date_trunc('month', ordered_at) is right, but leave the raw date in SELECT and the familiar error comes back. Add ordered_at to GROUP BY and you get eight rows, one per order date, because every order here has a different date. The monthly report turns into a daily one. Drop the raw date and three months remain:

   month    | orders |  total
------------+--------+---------
 2026-09-01 |      3 |  590.00
 2026-10-01 |      3 | 1215.00
 2026-11-01 |      2 |  335.00
(3 rows)

The GROUP BY error stops the query before it returns anything. A wrong grouping runs and returns a wrong result that someone pastes into a spreadsheet.

Which value did you want?

The fix depends on what product was supposed to hold. There are four common answers, and each leads to a different query.

Every product in one field. Then the column needs an aggregate too, just like amount. string_agg joins the values into a string:

SELECT customer_id,
       string_agg(product, ', ' ORDER BY product) AS products,
       sum(amount) AS total
FROM orders
GROUP BY customer_id;

 customer_id |         products          |  total
-------------+---------------------------+---------
           1 | keyboard, keyboard, mouse |  590.00
           2 | cable, monitor            | 1125.00
           3 | cable, headset, mouse     |  425.00
(3 rows)

Three rows, matching the question.

Watch out for max(product), which also silences the error. For Anna it returns mouse, because “mouse” sorts last alphabetically, even though the most expensive thing she bought was a keyboard. Asking for the biggest purchase needs a different tool from an aggregate.

The product from the biggest order. That’s a question about one specific row per group, and PostgreSQL has DISTINCT ON for it. It keeps the first row of each group in the order you give:

SELECT DISTINCT ON (customer_id) customer_id, product, amount
FROM orders
ORDER BY customer_id, amount DESC;

 customer_id | product  | amount
-------------+----------+---------
           1 | keyboard |  250.00
           2 | monitor  | 1100.00
           3 | headset  |  310.00
(3 rows)

This time Anna gets the keyboard, because it was the most expensive. She bought two keyboards at 250, though, so two of her rows tie. DISTINCT ON picks one and doesn’t promise which. If the result has to be repeatable, add a tie-breaking column such as id to ORDER BY.

Data about the customer, not the order. This is the most common case in practice: you want the name and city from customers next to the total. Here PostgreSQL has a rule that’s easy to miss. If you group by a table’s primary key, you can select its other columns without listing them, because the key determines them:

SELECT c.id, c.name, c.city, sum(o.amount) AS total
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;

 id |  name  |  city  |  total
----+--------+--------+---------
  1 | Anna   | Kraków |  590.00
  2 | Bartek | Gdańsk | 1125.00
  3 | Celina | Poznań |  425.00
(3 rows)

Group by the name instead of c.id and the error is back:

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

A name isn’t a key. Two Annas from different cities are a perfectly possible state for this table, and the database doesn’t guess.

The rule covers the primary key and nothing else. I added an email column with UNIQUE NOT NULL, which also identifies a customer, and grouping by it still fails with column "c.name" in the message. PostgreSQL only recognises functional dependency on a primary key. When you group by something else and need the customer’s columns, either list them in GROUP BY knowing they won’t split anything, or compute the totals in a subquery and join the customers to it:

SELECT c.name, c.city, t.total
FROM customers c
JOIN (SELECT customer_id, sum(amount) AS total
      FROM orders
      GROUP BY customer_id) t ON t.customer_id = c.id;

Same three rows, with name, city and total. The subquery does one job, counting, and the outer query attaches the description. In longer reports that split reads better than one GROUP BY with ten columns, half of them there only to keep the parser quiet.

Every order, with the customer’s total next to it. If you want to keep all the rows, GROUP BY is the wrong tool, because folding rows is its whole job. A window function computes the group total and folds nothing:

SELECT customer_id, product, amount,
       sum(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM orders;

 customer_id | product  | amount  | customer_total
-------------+----------+---------+----------------
           1 | keyboard |  250.00 |         590.00
           1 | mouse    |   90.00 |         590.00
           1 | keyboard |  250.00 |         590.00
           2 | monitor  | 1100.00 |        1125.00
           2 | cable    |   25.00 |        1125.00
           3 | mouse    |   90.00 |         425.00
           3 | headset  |  310.00 |         425.00
           3 | cable    |   25.00 |         425.00
(8 rows)

Eight rows, every order, each with the right customer total. product doesn’t need to appear in any clause, because no row was merged with anything and each keeps its own values.

One question before you add the column

Before you add a column because the message says it must appear in the GROUP BY clause, say in one sentence what one row of the result is. “One row is one customer” means the customer is in GROUP BY and every other column is either an aggregate or depends on the customer’s key. “One row is one order” means a window function, and “one row is a chosen order per customer” means DISTINCT ON. Adding the column is right only when it belongs to the answer to “what is one row”, and wrong when it’s there because leaving it out blocks the query.

Aggregation and window functions are two separate chapters in the DevJourney for SQL curriculum, “Aggregation” and “Window functions”. You write the queries for both on the PostgreSQL 17 built into the app, against a database that already has data in it.