What does GROUP BY actually produce, and how is it different from SELECT DISTINCT?
GROUP BY produces one row for each group, and the calculation is worked out for each of those rows.
That is the whole idea. You take all the rows, you split them into piles by the value of the grouping column, and then each pile gives you one row in the answer. Inside a group you can ask questions such as how many rows are in it, what the sum is, what the smallest is, and what the average is.
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, 'paid', 250.00),
(2, 1, 'paid', 120.00),
(3, 1, 'cancelled', 60.00),
(4, 2, 'paid', 900.00);
-- One row per customer.
SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total
FROM orders
GROUP BY customer_id;
So how is that different from SELECT DISTINCT? In three ways.
The first one is what comes out. The DISTINCT gives you the distinct combinations of the columns you selected, and nothing else. The GROUP BY gives you one row per group and lets you calculate something about each group.
The second one is how many rows there are. The DISTINCT gives you one row for each different combination. The GROUP BY gives you one row for each different value of the grouping column, even when the calculation comes out the same for two groups.
-- The distinct statuses.
SELECT DISTINCT status FROM orders;
-- One row per status, with a count.
SELECT status, COUNT(*) AS order_count
FROM orders
GROUP BY status;
The third one is that the GROUP BY can group by things that are not in the answer at all, such as a whole expression. The DISTINCT can only work with the columns you selected, because it compares the rows that come back.
There is one rule that makes the GROUP BY work, and it is the rule that catches everybody. Every column in the answer has to be either a grouping column or a calculation. If you put another column in the select list, the engine cannot know which of the many values in the group it should give you, and it reports an error.
So the rule to remember is this. The GROUP BY picks the piles, and everything in the answer has to come from one pile or be a calculation over it.
The next question in this topic asks for the simplest use of it.