Summarising data.
WHAT GROUPING DOES
Collapses rows sharing values into one row per combination.
WHAT TO BE CAREFUL WITH
Nulls, which form their own group Counting, where counting a column ignores nulls and counting everything does not Averages, which ignore nulls rather than treating them as zero
WHY THAT LAST POINT MATTERS
An average over a column with nulls is an average of the present values only, which may not be intended.
WHAT DISTINCT COUNTING COSTS
Substantially more than ordinary counting, at scale.
WHAT APPROXIMATE COUNTING PROVIDES
A close estimate, far faster.
WHEN THAT IS ACCEPTABLE
Exploratory work and dashboards where exactness is not required.
WHAT GROUPING SETS PROVIDE
Several groupings in one query.
WHAT ROLLUP AND CUBE PROVIDE
Subtotals and totals across combinations, without separate queries.
WHAT FILTERED AGGREGATES PROVIDE
Aggregating only rows meeting a condition, within one query.
WHY THAT IS USEFUL
Several conditional measures in one pass, rather than several queries joined.
WHAT TO VERIFY ALWAYS
That a total matches a figure you already know.
WHY
It catches fan-out, wrong filters and wrong grain immediately.
