Which Columns Must GROUP BY Include?

The rule is that every column in the SELECT list must either be in the GROUP BY, be wrapped in an aggregate, or be functionally dependent on the grouping columns. If a column appears raw and is not grouped, the query is asking for one value per group, but the rows in the group have different values — so the result would be ambiguous.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status VARCHAR(20) NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO orders (id, customer_id, status, amount) VALUES
  (1, 1, 'shipped', 120.00),
  (2, 1, 'shipped', 340.50),
  (3, 1, 'pending',  90.00),
  (4, 2, 'shipped',  75.25);

-- customer_id and the aggregates are fine.
SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

Two rows come back: customer 1 with three orders totalling 550.50, and customer 2 with one order of 75.25.

Now imagine adding status to the SELECT list without grouping it. Customer 1's group contains three rows with two different statuses, so the query would have to invent a single status for the group. PostgreSQL and MySQL in ONLY_FULL_GROUP_BY mode reject that with an error. SQLite is more permissive: it accepts the query and picks an arbitrary value from the group — usually the last row it happened to read — which means the same query can return different statuses on different runs. That silent behaviour is far worse than an error, and it is worth naming in an interview.

The functional dependency part is the modern relaxation. If the grouping column is the primary key of the table, every other column of that table is functionally dependent on it, so GROUP BY customer_id is enough to select status — as long as the grouping column really is unique. Group by a non-unique customer_id and the dependency no longer holds.

The practical advice: group by everything you select raw, unless you have a genuine reason not to. If you are aggregating and also want a raw attribute, the standard fix is to add it to the GROUP BY, or to wrap it in an aggregate such as MIN or MAX and decide explicitly which row you meant.

List the grouping columns in the GROUP BY, separated by commas, and put the same columns in the SELECT list. The result has one row per distinct combination of the two columns — not one row per value of each column separately.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status VARCHAR(20) NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO orders (id, customer_id, status, amount) VALUES
  (1, 1, 'shipped',   120.00),
  (2, 1, 'shipped',   340.50),
  (3, 1, 'pending',    90.00),
  (4, 2, 'shipped',    75.25),
  (5, 2, 'cancelled',  15.00),
  (6, 3, 'pending',   240.00);

SELECT customer_id,
       status,
       COUNT(*) AS order_count,
       ROUND(SUM(amount), 2) AS total_amount
FROM orders
GROUP BY customer_id, status
ORDER BY customer_id, status;

Four rows come back: customer 1 with pending (one order, 90.00) and shipped (two orders, 460.50), customer 2 with cancelled (one order, 15.00) and shipped (one order, 75.25), and customer 3 with pending (one order, 240.00).

Note what the combination does. Customer 1 appears twice rather than once, because the two statuses are separate groups; grouping by customer_id alone would have collapsed those two groups into one row with three orders totalling 550.50. The output also includes combinations that do not exist — there is no customer 3 with shipped status — and that is correct: GROUP BY reports the groups that exist, not the full cross product of the grouping columns. If you need the full grid with zeroes, you have to build it separately, which is a different piece of work.

The other thing to watch is the index. A GROUP BY on two columns benefits from a composite index with those columns in the same order, (customer_id, status). If the index is on (status, customer_id) instead, the grouping can still use it in some cases, but the leading column has to match the grouping prefix for the engine to skip the sort.