When do you need to group by more than one column, and what goes wrong if you group by too many?
You need more than one column when the answer has to say two things about each group at once.
So what goes wrong if you group by too many? Two things.
The first one is that the number of groups grows. If you group by the customer and the status you get a handful of groups per customer. If you group by the customer, the status, the day, the payment method and the city, then most of the groups have one row in them, and the report is a list of orders with some extra words on it.
The second one is that a group with one row in it makes the averages meaningless. The average of one order is that order, and the report says nothing about any pattern.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
status VARCHAR(20) NOT NULL,
amount DECIMAL(10, 2) NOT NULL
);
So the test to apply is this. Look at the number of rows that come back, and look at the average number of rows in each group. If the answer is nearly one, then you have grouped by too much and you should take a column out.
There is one more thing about grouping by more than one column that is worth knowing. The order of the columns in the group by does not change the groups that you get, it only changes the order that they come back in and the order that the engine does the work in. So GROUP BY customer_id, status and GROUP BY status, customer_id give you the same groups in the same answer, and the engine will usually turn them into the same plan.
The next two questions in this topic ask for a two column report and for a report with subtotals.