Grouping by Multiple Columns

You need multiple grouping columns whenever the answer you want has more than one dimension. "Revenue per customer" is one dimension. "Revenue per customer per status" is two. The rule is that the grouping columns are the dimensions of the report, and everything else is a measure computed on each combination.

Grouping by too many columns fragments the result. Add a third dimension and each group gets smaller, until you have one row per order and the aggregates are pointless. The symptom is a row count that looks far too high and totals that do not match the summary you expected. The fix is to group by exactly the dimensions the report needs and to aggregate the rest.

Grouping by too few columns is the opposite failure and it is quieter: rows from genuinely different categories get merged into one group, and the aggregate describes a population that does not exist. Adding a grouping column that looked redundant is often the right fix.

There is a subtlety about NULL in a grouping column. NULL groups together, so all rows where the column is missing collapse into a single group with a NULL label — which is usually what you want, but it means a NULL group is not excluded from a report unless you filter it.

On performance, the grouping columns should be the leading columns of a composite index if you have one, so the engine can produce the groups already sorted rather than sorting the whole table. And when you group by a date, remember that grouping by the raw timestamp column produces one group per distinct timestamp, which is almost never what a report wants — group by a truncated period instead.

Two grouping columns in the GROUP BY, both in the SELECT list, and the aggregates alongside them. The ORDER BY follows the same columns so the output reads consistently.

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),
  (7, 3, 'pending',    60.00);

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

Five rows come back. Customer 1 has one pending order worth 90.00 and two shipped orders worth 460.50, so 3 orders and 550.50 in total across the two rows. Customer 2 has one cancelled order of 15.00 and one shipped order of 75.25. Customer 3 has two pending orders worth 300.00.

That output is correct but it is not a customer-level report, and it is worth saying why. Because the report is at customer-and-status granularity, adding up the revenue column gives you the customer total only if you also add up across statuses. The three customer totals here are 550.50, 90.25 and 300.00, and you get them by either grouping by customer alone or by aggregating the result of this query again.

That double grouping is a perfectly good pattern, and it often reads better than a single wider query. A CTE can compute the detail rows — a CTE named by_customer_status that selects customer_id, status, COUNT(*) and SUM(amount) grouped by the two columns — and an outer query can then group that CTE by customer_id alone and sum the two measures. Use this shape when the detail rows are needed in the report as well as the totals.

SQLite does not support GROUPING SETS, ROLLUP or CUBE, so the portable way to build subtotals is to UNION ALL several grouped queries: one for the detail level, one for each subtotal level, and one for the grand total.

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,
       status AS label,
       'detail' AS level,
       ROUND(SUM(amount), 2) AS revenue
FROM orders
GROUP BY customer_id, status

UNION ALL

SELECT customer_id,
       'ALL statuses' AS label,
       'subtotal' AS level,
       ROUND(SUM(amount), 2) AS revenue
FROM orders
GROUP BY customer_id

UNION ALL

SELECT NULL AS customer_id,
       'ALL customers' AS label,
       'grand total' AS level,
       ROUND(SUM(amount), 2) AS revenue
FROM orders

ORDER BY customer_id, level, label;

Seven rows come back: four detail rows — customer 1 shipped at 460.50 and pending at 90.00, customer 2 shipped at 75.25 and cancelled at 15.00 — two subtotal rows at 550.50 for customer 1 and 90.25 for customer 2, and one grand total of 640.75.

The structure of the trick is that each branch produces the same three columns with compatible types, and UNION ALL stacks them. The level column is the important addition: without a label telling you which branch a row came from, the output is unreadable, because a subtotal row looks exactly like a detail row.

Where the engines do support it, the built-in is much shorter. PostgreSQL and MySQL have GROUPING SETS, ROLLUP and CUBE, and Oracle has ROLLUP — GROUP BY ROLLUP(customer_id, status) produces exactly this output, with the subtotal rows identified by a NULL in the rolled-up column. The GROUPING function then tells you which rows are subtotals. SQL Server has no ROLLUP at all and uses GROUPING SETS from 2012 onwards.

One caveat for the UNION ALL version: the grand total branch selects NULL for customer_id, so if the column is typed as an integer in a stricter engine you may need an explicit cast. And when you add a fourth level, the query grows — at that point a small reporting table or a view is often the better answer.