GROUP BY and COUNT
Most business questions ask for a total per something, such as tests per make or failures per month. GROUP BY turns many rows into one row per group.
How it works
GROUP BY puts rows that share the same key value into one group. Each group becomes one output row, and an aggregate function such as COUNT summarizes the rows inside it.
- Every column in
SELECTmust be a grouping key or sit inside an aggregate function. COUNT(*)counts the rows in each group.COUNT(column)counts only the rows where that column is notNULL.- Name an aggregate with
AS.ORDER BYcan then use that name. GROUP BYdoes not promise any row order, so addORDER BYwhen the sequence matters.
Common mistake
A column in SELECT that is neither in GROUP BY nor inside an aggregate is an error. Adding it to GROUP BY removes the error but makes the groups finer, so the rows and the counts change.
Worked example
SELECT test_result, COUNT(*) AS test_count
FROM `learn.mot.fact_mot_test`
WHERE completed_date BETWEEN '2024-02-01' AND '2024-02-29'
GROUP BY test_result
ORDER BY test_resultThe February 2024 tests fall into two groups, one per result.
The output has one row per group, and test_count is the number of tests in that group.