Multi Column Grouping and Grouping Rules

Concept

Grouping by more than one column produces one row per combination of their values. Adding a column to GROUP BY always makes the result finer grained and the groups smaller.

Syntax

SELECT   col1, col2, aggregate_function(col3)
FROM     table_name
GROUP BY col1, col2;

Example

-- One row per department and status combination
SELECT dept_id,
       status,
       COUNT(*)              AS people,
       ROUND(AVG(salary), 2) AS avg_salary
FROM   employees
GROUP BY dept_id, status
ORDER BY dept_id, status;

-- Grouping by an expression: revenue per month
SELECT EXTRACT(YEAR  FROM order_date) AS order_year,
       EXTRACT(MONTH FROM order_date) AS order_month,
       COUNT(*)                       AS orders,
       SUM(total)                     AS revenue
FROM   orders
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)
ORDER BY order_year, order_month;

Explanation

The order of columns in GROUP BY does not change which groups are produced - only ORDER BY controls presentation. It can matter for performance, though: if an index exists on (dept_id, status), grouping in that order may let the engine read the data already grouped and skip a sort.

Grouping by an expression requires repeating the expression. That is verbose, which is a good reason to compute it once in a CTE:

WITH monthly AS (
    SELECT DATE_FORMAT(order_date, '%Y-%m') AS ym, total
    FROM   orders
)
SELECT ym, COUNT(*) AS orders, SUM(total) AS revenue
FROM   monthly
GROUP BY ym
ORDER BY ym;

Subtotals: ROLLUP, CUBE and GROUPING SETS

-- MySQL / MariaDB syntax
SELECT dept_id, status, COUNT(*) AS people
FROM   employees
GROUP BY dept_id, status WITH ROLLUP;

-- Standard, PostgreSQL, SQL Server, Oracle
SELECT dept_id, status, COUNT(*) AS people
FROM   employees
GROUP BY ROLLUP (dept_id, status);

Subtotal rows carry NULL in the columns being totalled over, which is indistinguishable from a real NULL. The GROUPING() function tells them apart, and is available wherever ROLLUP is.

Important rules

• Adding a grouping column can only increase the number of result rows.
• Every non aggregated select list column must appear in GROUP BY, expressions included.
• Grouping by a column with high cardinality - an id, a timestamp - produces one row per input row and defeats the purpose.
• Group counts come from the data. Combinations that never occur do not appear as zero rows.

Common mistakes

• Grouping by a raw DATETIME when the intent was per day, and getting one group per row.
• Adding a column to SELECT and forgetting to add it to GROUP BY.
• Reading a ROLLUP subtotal row as a real data row because both show NULL.
• Assuming CUBE and GROUPING SETS exist in MySQL. They do not.

Best practices

• Compute grouping expressions once in a CTE, then group by the alias.
• Match the GROUP BY column order to a supporting index where one exists.
• Use GROUPING() to label subtotal rows in reports.
• Where the product lacks ROLLUP, a UNION ALL of the detail query and the total query does the same job explicitly.

Practice

1. Return order counts per customer per status.
2. Produce monthly revenue for 2024 with a grand total row.
3. Explain why grouping orders by order_date gives almost as many rows as the table has.