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.
GROUP BY make, fuel_typegives one row for each combination of the two values that occurs in the data.GROUP BY ALLuses everySELECTcolumn that is not an aggregate as a grouping key.HAVINGandORDER BYwork the same way as with an explicit list.- A
STRUCTfield such asvehicle_snapshot.fuel_typecan be a grouping key, and the output column takes the field name.
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_sourcetest_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.