Aggregation — summarising data by groups — is the heart of most analysis.
Pivot Tables
Spreadsheet pivot tables let you drag fields into rows, columns and values to summarise quickly: sales by region and month, counts by category.
In SQL
GROUP BY with aggregate functions such as SUM, COUNT, AVG, MIN and MAX. HAVING filters groups.
In pandas
groupby with aggregation, and pivot_table for spreadsheet-style summaries.
Common Mistakes
- Averaging averages: the average of regional averages isn't the overall average when regions differ in size. Use weighted averages or recompute from raw data.
- Counting duplicates: use distinct counts where needed.
- Mixing grains: joining daily data with monthly data inflates sums.
- Summing ratios: add numerators and denominators separately, then divide.
- Hidden filters: check pivot table filters before sharing.
Simpson's Paradox
A trend in every group can reverse when groups are combined. Look at both levels.
Check Totals
Make sure grand totals match the source data.