Summarising data.
WHAT AGGREGATION DOES
Reduces many rows to a summary.
WHAT THE COMMON FUNCTIONS ARE
Count Sum Average Minimum and maximum
WHAT GROUPING DOES
Produces one summary row per distinct value.
WHAT THE RULE IS
Every selected column must be aggregated or grouped.
WHY MYSQL SOMETIMES ALLOWS OTHERWISE
A permissive mode, which returns an arbitrary value.
WHY THAT IS DANGEROUS
The result looks correct and is not.
WHAT TO DO
Enable strict grouping behaviour.
WHAT THE DIFFERENCE BETWEEN FILTERING BEFORE AND AFTER GROUPING IS
One reduces rows before summarising; the other filters the summaries.
WHY THAT MATTERS FOR PERFORMANCE
Filtering first is far cheaper.
WHAT COUNTING ROWS VERSUS COUNTING A COLUMN DIFFERS IN
Counting a column ignores empty values.
WHY THAT CATCHES PEOPLE
The two give different answers on the same data.
WHAT AVERAGES IGNORE
Empty values, which changes the divisor.
WHAT TO BE CAREFUL WITH
Averaging a column that is sometimes missing Summing a ratio, which is meaningless
WHAT TO DO INSTEAD OF SUMMING RATIOS
Sum the numerators and denominators, then divide.
WHAT TO INDEX
The grouped column, which can avoid a sort.
