Which columns have to appear in the GROUP BY, and what happens when one does not?
Every column in the select list has to be either a grouping column or a calculation. That is the rule.
So if you put a plain column in the select list and it is not in the group by, then the engine has a problem. The group has many rows in it, and the column has a different value in each of those rows, and there is no way to pick one of them.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
status VARCHAR(20) NOT NULL,
amount DECIMAL(10, 2) NOT NULL
);
-- This does not work. status is not grouped and it is not a calculation.
-- SELECT customer_id, status, SUM(amount) FROM orders GROUP BY customer_id;
-- This works. The status is part of the grouping.
SELECT customer_id, status, SUM(amount) AS total
FROM orders
GROUP BY customer_id, status;
So what happens when one does not appear? Most engines report an error and refuse to build the query. The message usually says that the column has to appear in the group by or be used inside an aggregate function.
Some engines let it through. MySQL used to allow it and to pick an arbitrary value from the group, and it can still do so if you turn the strict setting off. That setting is called ONLY_FULL_GROUP_BY and it should be on, because the arbitrary value that comes back is not something you can rely on. It might be the first value, it might be the last one, and it can change when the plan changes.
There are two ways to deal with it when you genuinely need that column in the answer.
The first one is to add it to the group by. That changes the shape of the answer, because you now get one row per group and per value of that column, and that is usually what you wanted anyway.
The second one is to wrap it in an aggregate. The function MAX(status) gives the largest value in the group, and the function MIN(status) gives the smallest. That is a real answer, and it says something about the group, unlike an arbitrary pick.
-- One row per customer, with the highest status in each group.
SELECT customer_id, MAX(status) AS highest_status, SUM(amount) AS total
FROM orders
GROUP BY customer_id;
So the rule to remember is this. Every plain column in the answer goes in the group by, and if you do not want a row per value of that column then wrap it in an aggregate and say which one you want.
The next question in this topic asks for a query that groups by two columns.