Data Aggregation and Summarisation | Grain, Rollups, Grouping, Denominators, Information Loss and Simpson’s Paradox

Data aggregation combines lower-level observations into higher-level groups or summaries. Data summarisation reduces a larger body of information into statistics, distributions or representations intended to preserve what matters for a particular question. Both are forms of controlled information loss: they make data easier to understand and compute with by discarding detail.

Aggregation is useful because it forgets detail. It becomes dangerous when the receiver forgets what was lost.

Daily sales, average test scores, monthly attendance, conversion rates, medians, percentiles and dashboards are all summaries. They can reveal patterns that individual rows hide. They can also conceal variation, reverse apparent trends, double-count entities or create false comparisons when grain and denominator are unclear.

ARTICLE ID: DATA.MANAGEMENT.051
Canonical function: reduction of detailed observations into reproducible group-level representations
Owner boundary: this article owns aggregation mechanics, grain and information loss. Semantic Layers and Metric Governance owns governed metric definitions; Data Warehousing and Analytics owns analytical architecture; Data Sampling and Statistical Representativeness owns inference from subsets.

The Simple Answer

A trustworthy aggregation route is:

Define Grain → Define Population → Group → Apply Measure → Preserve Denominator → Quantify Variation → Record Filters and Time Basis → Validate Rollup → Publish Summary + Route Back to Detail

The summary should be reproducible from the underlying records and explicit enough that another analyst can tell exactly which observations contributed.

Grain Comes First

Grain defines what one row or observation represents.

one transaction;
one student per lesson;
one customer per day;
one product per store per hour;
one sensor reading per minute.

If grain is unclear, aggregation can double-count or omit information even when the formula itself is correct.

Changing Grain Changes Meaning

A transaction-level dataset can be rolled up to customer, branch, day, month or product. Each level answers a different question.

“Average transaction value” and “average customer spend” are not interchangeable because their grains differ.

Grouping

Grouping partitions observations into categories before summary statistics are calculated.

by class;
by school level;
by region;
by product;
by month;
by customer segment.

Group definitions should come from governed dimensions or explicit transformation rules rather than ad hoc labels that change between reports.

Rollups

A rollup aggregates data from a finer level to a coarser level.

For example:

Lesson → Day → Week → Term → Year

Not every measure can be rolled up safely through every level.

Additive Measures

An additive measure can be summed across relevant dimensions without changing its meaning.

Examples often include transaction revenue or item quantity, provided the underlying records are non-overlapping and expressed in compatible units.

Semi-Additive Measures

Some measures can be summed across some dimensions but not others.

An account balance can be summed across accounts at one point in time, but summing daily balances across a month usually does not represent a meaningful monthly balance.

Non-Additive Measures

Rates, percentages, averages and many ratios are generally non-additive.

Adding two conversion rates does not produce a valid overall conversion rate. The underlying numerators and denominators must usually be recombined first.

Never Average Averages Blindly

Suppose Class A has an average score of 80 across 10 students and Class B has an average of 60 across 30 students. The overall average is not 70.

The correct overall mean weights each class by its number of contributing observations:

(80 × 10 + 60 × 30) ÷ 40 = 65

The denominator is part of the metric.

Denominators

Rates and percentages are only interpretable when the denominator is explicit.

attendance among enrolled students;
conversion among eligible visitors;
defects among inspected units;
pass rate among students who sat the examination;
response rate among invited participants.

Changing the denominator can change the result even when the numerator is unchanged.

Numerator and Denominator Must Share a Population

A numerator from one population divided by a denominator from another can create plausible-looking nonsense.

Population filters, time windows and eligibility rules should be aligned before calculating a rate.

Weighted Averages

Weighted averages assign different contribution weights to observations or groups.

Weights can represent population size, duration, exposure, sampling probability or business importance. The meaning of the weight must be explicit.

Weights Can Change the Question

An unweighted average of school-level pass rates answers “what is the average school pass rate?” A student-weighted average answers “what proportion of students passed?”

Both can be legitimate. They are not the same metric.

Mean

The arithmetic mean uses every numerical value and is sensitive to extreme observations.

It is useful when totals and balance around the centre matter, but can poorly represent skewed distributions.

Median

The median is the middle observation after ordering. It is more robust to extreme values and often useful for skewed data such as response time or income.

The median does not preserve total quantity and should not replace the mean automatically.

Mode

The mode identifies the most frequent value or category. It can be useful for categorical data but may be unstable when several categories have similar frequency.

Percentiles and Quantiles

Percentiles preserve more distributional information than a single average. They help answer questions about tails and spread.

For service latency, the 95th or 99th percentile can reveal slow experiences hidden by a low mean.

Quantile Definitions Can Differ

Software packages can use different interpolation conventions for sample quantiles. Where exact reproducibility matters, the quantile algorithm should be documented.

Variance and Standard Deviation

Measures of spread help show how much observations vary around a centre.

Averages without dispersion can hide whether most values cluster tightly or are spread widely.

Min and Max

Minimum and maximum preserve extremes but are sensitive to erroneous values and do not describe the rest of the distribution.

They are particularly useful in operational monitoring where one extreme can matter more than the average.

Counts

A count is only meaningful when the counted unit is clear.

rows;
distinct students;
distinct households;
events;
sessions;
transactions;
products.

Counting rows is not automatically counting entities.

Distinct Counts

Distinct counts depend on identity resolution. If one customer has two IDs, a distinct-ID count overstates customers. If two people share one household ID, it can understate them.

See Data Deduplication and Entity Resolution.

Double Counting

Double counting occurs when the same underlying entity or event contributes more than once to an aggregate unintentionally.

Common causes include:

joining one-to-many tables before summing;
duplicate events;
overlapping categories;
combining already-aggregated totals;
replaying transactions without deduplication.

Join Explosion

A join can multiply rows. Joining one order to five line items and then summing an order-level total repeats that total five times unless grain is managed explicitly.

Aggregation errors often begin before the GROUP BY clause.

Hierarchies

Dimensions often form hierarchies:

day → month → quarter → year;
class → level → school;
SKU → product → category;
branch → region → country.

Rollups should follow well-defined parent-child relationships. Overlapping or changing hierarchies need versioning.

Slowly Changing Dimensions

A student’s class, a customer’s segment or a product’s category can change over time.

Historical aggregation must decide whether to use the classification that was true at event time or today’s classification. Both can be useful, but they answer different questions.

Current View vs Historical Truth

Reclassifying all historical sales into today’s product hierarchy can improve current management reporting while changing the historical categorisation originally used.

Reports should state which temporal interpretation they use.

Time Windows

Every aggregate over time needs a boundary.

calendar day;
rolling 24 hours;
week;
term;
fiscal month;
session;
cohort lifetime.

“Monthly” does not fully define the period if organisations use different calendars or time zones.

Late Data and Restatement

Late-arriving records can change previously published aggregates.

The system should decide whether to restate history, freeze prior reports, publish correction deltas, or distinguish preliminary from final aggregates.

Preliminary vs Final

A useful reporting lifecycle can include:

PRELIMINARY;
RECONCILED;
FINAL;
RESTATED.

This prevents a number from appearing immutable when the underlying reporting window is still accepting corrections.

Simpson’s Paradox

Simpson’s paradox describes situations where an aggregate relationship reverses or substantially changes after data is separated into relevant groups.

This can happen because group sizes and underlying rates differ. An overall improvement can coexist with deterioration inside every subgroup, or the reverse.

The lesson is not that aggregates are wrong. It is that grouping structure can change interpretation.

A Simple Illustration

Suppose two tutoring programmes serve different proportions of easy and difficult cases. Programme A may have a higher success rate within both difficulty groups yet a lower overall rate because it serves far more difficult cases.

The aggregate answers one question. The stratified comparison answers another. Neither should be interpreted without the composition of the groups.

Ecological Fallacy

The ecological fallacy occurs when relationships observed for groups are assumed to hold for individuals.

A school with a high average score does not imply that every student in the school scored highly. Group statistics should not be assigned automatically to individual members.

Aggregation Bias

Coarse aggregation can hide important variation or create apparent relationships that differ at finer levels.

Before deciding that one summary tells the story, inspect relevant subgroup and distributional views.

Information Loss

Every summary discards information.

a mean discards individual values;
a monthly total discards daily timing;
a category count discards ordering;
a histogram discards exact values within bins;
a top-10 table discards the tail.

Summaries should be chosen according to which information can safely be lost.

Distributions Before Single Numbers

Where variation matters, show distributions, quantiles or subgroup summaries alongside averages.

One average can conceal multimodal populations, long tails and extreme outliers.

Histograms

Histograms group numerical values into bins. Bin width and boundaries affect visual interpretation.

Different bin choices can make the same data appear smoother or more fragmented.

Top-N Summaries

Top-N lists focus attention but hide the long tail.

They should state whether ties are included and how the remainder is represented.

Approximate Aggregation

Large systems may use approximate algorithms for distinct counts, quantiles or sketches to reduce memory and computation.

Approximation can be entirely appropriate when error bounds are known and the receiver does not require exact values.

Approximate results should be labelled as such rather than presented as exact simply because they display many digits.

Rounding

Rounding improves readability but can create reconciliation differences if rounded components are summed.

Store calculations at sufficient precision and apply presentation rounding at the appropriate layer.

Suppression

Small group counts may be suppressed to reduce privacy or disclosure risk.

Suppression creates missing aggregate cells and can sometimes allow values to be inferred from totals. Release design should consider complementary suppression or other privacy controls where necessary.

Aggregation and Privacy

Aggregation can reduce exposure by reporting group statistics instead of individual records, but it is not a universal anonymisation technique.

Small groups, repeated queries and external information can still reveal sensitive facts.

See Synthetic Data and Privacy-Preserving Data.

Aggregation and Missing Data

Summary statistics should state how missing observations are treated.

excluded from denominator;
treated as zero;
imputed;
reported separately;
structurally not applicable.

Different treatments can materially change rates and averages.

See Missing Data and Imputation.

Aggregation and Sampling Weights

Survey and sampled data often require weights before population-level aggregation.

Raw sample percentages can misrepresent the target population when inclusion probabilities differ.

Materialised Aggregates

Frequently used summaries may be precomputed and stored for performance.

Materialised aggregates should retain source lineage, metric version, grouping dimensions, refresh time and restatement state.

See Data Caching and Materialisation.

Reconciliation

Important aggregates should reconcile to lower-level records under known rules.

A total that cannot be reproduced from source data is not merely inconvenient; it is an accountability problem.

Aggregation Contracts

A reusable summary should declare:

grain;
population;
numerator;
denominator;
grouping dimensions;
time basis;
missing-value treatment;
weighting;
rounding;
source versions;
metric version.

This is the bridge between aggregation mechanics and metric governance.

Data Cubes

Analytical systems can precompute combinations of measures across dimensions such as time, product and region.

Cubes speed repeated slicing and rollup but can become expensive as dimensions multiply. Not every theoretical combination is useful.

Drill Down

A strong analytical experience lets users move from aggregate to finer detail where authorised.

Drill-down is the practical return path that helps a receiver investigate why a summary changed.

Drill Through

Drill-through connects a summary to the underlying records or related detailed product.

Access controls still apply; an aggregate dashboard should not automatically grant access to sensitive individual rows.

Aggregation and AI

AI systems often answer questions by generating summaries over retrieved or queried data. The system should preserve the same aggregation contract a human analyst needs: grain, population, time window, denominator and source.

A fluent answer that averages averages incorrectly is still wrong.

Natural-Language Analytics

Questions such as “What was our average improvement?” are underspecified.

average across students or classes?
absolute points or percentage change?
which period?
only students with both tests?
weighted by enrolment?

Semantic layers and governed metrics should reduce the need for an AI or analyst to guess these choices.

Education Example

A school compares examination performance across classes. One dashboard shows class means, but the classes differ in size and prior attainment. A second view shows student-weighted overall performance, score distributions and prior-attainment bands.

The additional views do not make the first metric invalid. They stop one aggregate from carrying more meaning than it can support.

Commerce Example

A retailer calculates conversion rate by channel. Mobile appears lower overall than desktop. After separating new and returning customers, mobile has higher conversion within both groups because the channel mix differs sharply.

The apparent reversal is a composition effect. The business needs both the overall channel outcome and the stratified explanation.

Operations Example

A service reports average response time of 300 milliseconds. The median is 120 ms and the 99th percentile is 4.2 seconds. The average alone hid a severe tail experienced by a small but meaningful share of users.

Common Failure Modes

Grain forgotten: rows are summed at incompatible levels.
Average of averages: group sizes disappear.
Denominator drift: the population changes without the metric name changing.
Distinct ID equals distinct entity: identity defects distort counts.
Join explosion: one-to-many relationships multiply measures.
Mean equals distribution: tails and multimodality disappear.
Current hierarchy rewrites history silently: old categories are lost.
Late data ignored: preliminary aggregates look final.
Aggregate trend equals individual trend: ecological fallacy appears.
Privacy by aggregation assumed: small groups remain revealing.

An Aggregation Checklist

What does one source row represent?
What grain should the summary represent?
What population is included?
Which grouping dimensions apply?
Is the measure additive, semi-additive or non-additive?
What is the numerator?
What is the denominator?
Are weights required?
How are missing observations treated?
Could joins double-count rows?
Should distribution or percentiles accompany the centre?
Which time window and timezone apply?
Can late data restate the result?
Can the aggregate reconcile to underlying records?
What information is intentionally lost, and is that loss acceptable for the receiver?

A Maturity Ladder

Summed: basic totals and averages are computed.
Grain-aware: source and output grains are explicit.
Population-aware: numerators, denominators and filters are governed.
Distribution-aware: variation and tails accompany central summaries.
Temporal: hierarchies, windows and restatements preserve historical meaning.
Reconciled: aggregates reproduce from source evidence.
Privacy-aware: group release considers disclosure risk.
Adaptive: receiver questions and observed failure modes improve future summary design.

The Deeper Principle: Summaries Are Arguments About What Can Be Forgotten

Aggregation is not merely arithmetic. It is a decision about which distinctions no longer matter for the current question. When that decision is correct, millions of rows become understandable. When it is wrong, the summary can be precise, reproducible and still misleading.

Trustworthy summarisation therefore keeps the path back to grain, population, denominator and distribution. It lets the receiver benefit from compression without mistaking compression for the whole world.

Data Management Series

Final idea: aggregate boldly when detail is unnecessary, but keep the grain, denominator, variation and return path visible. A summary is trustworthy only when the receiver can tell both what it says and what it had to forget in order to say it.