What Does GROUP BY Produce?

GROUP BY collapses the input rows into one output row per distinct combination of the grouping columns, and the aggregate functions then summarise the rows inside each group. It is a two-stage operation: group first, then aggregate. The output has one row per group, and the number of columns is whatever you put in the SELECT list — the grouping columns plus whatever aggregates you ask for.

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);

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

Two rows come back: customer 1 with 3 orders totalling 550.50, and customer 2 with 2 orders totalling 90.25. The five input rows became two output rows.

How that differs from DISTINCT is about what happens to the rest of the row. SELECT DISTINCT customer_id FROM orders also returns two rows, but it does so by discarding duplicate rows and keeping one representative — it never adds a count or a sum, because it has no idea what to do with the rows it drops. GROUP BY keeps every input row inside its group and folds them into an aggregate, so it can answer questions that DISTINCT cannot: how many, how much, the average, the earliest, the latest.

That leads to a useful decision rule. If you want the unique values of something, DISTINCT is lighter and clearer. If you want anything about the population behind those values, you need GROUP BY. And if a query is producing an unexpected number of rows, GROUP BY is usually where the bug is: grouping by too many columns fragments the groups, and grouping by too few collapses rows that should have stayed separate.

Group by the customer and count the rows in each group. The count has to be of something in the group — COUNT(*) counts rows, COUNT(column) counts non-NULL values in that column.

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, 'shipped',   210.00);

SELECT customer_id,
       COUNT(*) AS order_count,
       SUM(amount) AS total_spent,
       MIN(amount) AS smallest_order,
       MAX(amount) AS largest_order
FROM orders
GROUP BY customer_id
ORDER BY order_count DESC, customer_id;

Three rows come back: customer 1 with 3 orders, customer 2 with 2 orders and customer 3 with 1 order. The ORDER BY puts the busiest customer first, with customer_id as the tiebreaker so the order is deterministic even if two customers have the same count.

One important detail: this query reports only customers who have at least one order, because GROUP BY runs on the rows that exist. If the report is supposed to include every customer with a count of zero for the ones who never ordered, you need to start from the customers table and join — SELECT c.id, COUNT(o.id) FROM customers c LEFT JOIN orders o ON o.customer_id = c.id GROUP BY c.id. That version also teaches you why the argument to COUNT matters: counting o.id gives 0 for a customer with no matching order, while counting * would give 1, because the outer join still produces a row with NULLs in it.

Also worth noting that the aggregates in the same SELECT list all describe the same group — you cannot mix MIN(amount) with MAX(created_at) and expect them to relate to the same row. They are separate summaries of the group, and any correlation between them is coincidental.