Fix "must appear in the GROUP BY clause"

You add one more column to a GROUP BY query and PostgreSQL stops you with: column "orders.order_date" must appear in the GROUP BY clause or be used in an aggregate function. The tempting fix is to add the column to GROUP BY until the error goes away. That makes the error disappear and often makes the numbers wrong. This page explains what the error is asking, and gives the fixes in the order you should consider them.

Step 1: see the error

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

ERROR:  column "orders.order_date" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT customer_id, order_date, count(*) AS orders
                            ^

The query says "one row per customer". Each customer has several orders with several dates, so which date should the row show? There is no single answer, and PostgreSQL will not guess. The error means you have not said which one you want.

Step 2: pick the fix that matches your question

Fix A: you want one value from the group. Aggregate it. The latest date:

SELECT customer_id, max(order_date) AS last_order, count(*) AS orders
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

 customer_id | last_order | orders
-------------+------------+--------
           1 | 2026-09-20 |      3
           2 | 2026-09-14 |      2
           3 | 2026-09-02 |      3
           5 | 2026-09-10 |      2
           6 | 2026-09-12 |      1
           8 | 2026-09-25 |      1
(6 rows)

Use min for the first, max for the last, count, sum or avg for numbers.

Fix B: you really want one row per customer and date. Add the column to GROUP BY. The question has changed, so the result has more rows.

SELECT customer_id, order_date, count(*) AS orders
FROM orders
WHERE customer_id = 1
GROUP BY customer_id, order_date
ORDER BY order_date;

 customer_id | order_date | orders
-------------+------------+--------
           1 | 2026-08-01 |      1
           1 | 2026-08-15 |      1
           1 | 2026-09-20 |      1
(3 rows)

Ana's three orders are now three rows with a count of 1. That is correct for "orders per customer per date", and wrong if you wanted her total.

Fix C: the extra column belongs to the grouped row. If you group by a table's primary key, PostgreSQL knows every other column of that table has one value for it, so you can select them without grouping on them.

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

 customer_id |    name    |  city  | orders
-------------+------------+--------+--------
           1 | Ana Reyes  | Manila |      3
           2 | Ben Cruz   | Cebu   |      2
           3 | Carla Diaz | Manila |      3
           4 | Dan Lim    |        |      0
           5 | Ella Tan   | Davao  |      2
           6 | Finn Go    | Cebu   |      1
           7 | Gina Ortiz |        |      0
           8 | Hugo Sy    | Manila |      1
(8 rows)

name and city are not in GROUP BY and no error appears, because customer_id is the primary key of customers. This rule is in the SQL standard, and not every database applies it. A query that works here may fail elsewhere.

Fix D: you want the detail row and the total side by side. GROUP BY collapses the rows, so it is the wrong tool. Use a window function and keep every row.

SELECT order_id, customer_id, order_date,
       count(*) OVER (PARTITION BY customer_id) AS customer_orders
FROM orders
WHERE customer_id IN (1, 2)
ORDER BY customer_id, order_date;

 order_id | customer_id | order_date | customer_orders
----------+-------------+------------+-----------------
        1 |           1 | 2026-08-01 |               3
        2 |           1 | 2026-08-15 |               3
       11 |           1 | 2026-09-20 |               3
        3 |           2 | 2026-08-03 |               2
       10 |           2 | 2026-09-14 |               2
(5 rows)

Which one to pick

Related course: SQL Intermediate.