Multiple grouping columns and GROUP BY ALL

Useful breakdowns often use two or three columns at once, such as make and fuel type together. Listing every column by hand in GROUP BY is where mistakes creep in.

How it works

Each different combination of the grouping columns becomes one group. GROUP BY ALL builds the list for you from the columns in SELECT that are not aggregates, so the query stays valid when you add or remove a column.

Common mistake

GROUP BY ALL follows the SELECT list. Adding a column to SELECT makes the groups finer and changes the row counts without any error, so check the number of rows after each column you add.

Worked example

SELECT test_result, data_source, COUNT(*) AS test_count
FROM `learn.mot.fact_mot_test`
WHERE completed_date BETWEEN '2024-08-01' AND '2024-08-31'
GROUP BY ALL
ORDER BY test_result, data_source

test_result and data_source are not aggregates, so GROUP BY ALL groups by both of them.

The output has one row for each pair of values that occurs in the August 2024 tests.

All BigQuery lessons