Quick answer
Data aggregation is the process of combining many raw records into summary values — counts, sums, averages, medians, percentiles — usually by grouping them over a time period or a dimension such as channel or device.
Formula: mean = sum ÷ count of non-NULL values; median = middle value of sorted rows; P90 = value below which 90% of rows fall (percentile_cont interpolates, percentile_disc takes the nearest rank)
What is data aggregation?
Data aggregation is the process of combining many raw records into summary values — counts, sums, averages, medians, percentiles — usually by grouping them over a time period or a dimension such as channel or device. The output is called aggregate data or a rollup: one row per group instead of one row per event.
Every aggregation makes two choices:
- Grouping — which records belong together. In SQL this is
GROUP BY: all orders from the same day, all sessions from the same channel, all events from the same user. - Aggregation function — how each group collapses into one number: count the rows, sum an amount, take the middle value.
SELECT channel, COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders
GROUP BY channel;
Two axes decide how coarse the result is: time (hour → day → week → month) and dimension (device → channel → total; or city → country, which is spatial aggregation). Aggregation can run in batch (a nightly job) or in real time (updated as events arrive); the grouping choices matter more than the schedule.
Aggregation is what makes data readable: nobody reviews a million event rows one by one. But every summary also deletes detail — the $900 order in the example below becomes invisible inside a daily average. So the working rule for this article: decide who needs to answer which question before choosing how coarse to aggregate, and keep raw data available for drill-down.
Common aggregation functions
The function you pick decides which question the summary answers — and which mistakes it can hide. The "watch out" column below uses the order data from the next section (Sample data), so every warning is checkable. Semantics follow the PostgreSQL aggregate-function documentation and SQLite's built-in aggregate functions.
| Function | Formula / definition | Question it answers | Watch out for |
|---|---|---|---|
| count | number of rows; count(*) counts all rows, count(x) skips NULL | How many events happened? | NULLs silently shrink count(x): 10 vs 8 below |
| count distinct | number of distinct values (users, orders) | How many people, not how many times? | Repeat events inflate plain count: 10 orders ≠ 7 buyers |
| sum | x₁ + x₂ + … + xₙ | What is the total volume or revenue? | One outlier row dominates: $900 of the $1,155 total |
| avg / mean | sum ÷ count of non-NULL values | What is a typical value, if the data is symmetric? | Skewed by outliers: 115.5 vs median 29 |
| median | middle value of sorted rows; percentile_cont(0.5) | What is a typical value when outliers exist? | Hides the tail completely |
| percentile (P90) | value below which 90% of rows fall | How bad is the tail? | Definition changes the answer: 126 (interpolated) vs 40 (nearest rank) |
| min / max | smallest / largest value | What is the range and worst case? | Max is one row, not a trend |
| mode | most frequent value (PostgreSQL mode()) | What is the most common category? | Unstable on small samples |
Two official definitions worth knowing precisely: PostgreSQL's percentile_cont(fraction) interpolates between adjacent sorted values, while percentile_disc returns the first sorted value whose position meets the fraction — no interpolation. And SQLite's avg(X) divides by the number of non-NULL inputs, not by the number of rows. Both differences change published numbers, as the next sections show. For when to prefer the middle value over the average, see mean vs median; for reading tails, see percentile.
Worked example: one day of orders, five answers
Sample data: a fictional store on 2026-11-10, ten orders (USD): 18, 22, 25, 25, 28, 30, 32, 35, 40, 900. Seven unique buyers: one placed 3 orders, one placed 2, five placed 1 each (3+2+5=10). The $900 order is a bulk purchase at 14:00.
The same ten rows, aggregated differently:
| Question | Aggregation | Computation | Answer |
|---|---|---|---|
| How much revenue? | sum | 18+22+25+25+28+30+32+35+40+900 | $1,155 |
| How many orders? | count | 10 rows | 10 |
| Typical order size (mean)? | avg | 1,155 ÷ 10 | $115.50 |
| Typical order size (median)? | median | sorted 5th and 6th values: (28+30) ÷ 2 | $29 |
| How bad is the tail? | P90 | cont: rank 0.9 × (10−1) = 8.1 → 40 + 0.1 × (900−40); disc: ceil(0.9 × 10) = 9th value | $126 vs $40 |
| Revenue per buyer? | sum ÷ count distinct | 1,155 ÷ 7 | $165 |
Three things to notice.
The mean misstates "typical." Behind the mean of 115.5, nine of the ten orders are $40 or less; the median of 29 is much closer to what most orders look like. The mean's job is total ÷ count, not "what most customers experience."
P90 depends on the definition. On identical data, the interpolating method gives 126 and the nearest-rank method gives 40 — a threefold gap. Always name the method when publishing a percentile, or two teams' P90 numbers will never match.
Per-order ≠ per-person. 115.5 vs 165: repeat buyers push the per-person figure up. "Average order value" and "average revenue per buyer" answer two different questions; the report title should say which one it is.
Outlier test: drop the $900 row. The sum becomes 255 and the mean 255 ÷ 9 = 28.33; the median of the remaining nine rows is 28. Adding the bulk order back moves the mean from 28.33 to 115.5 (×4.1) but the median only from 28 to 29. If your dashboard shows the mean alone, one wholesale order rewrites your "typical customer" story; the median barely moves. Neither number is wrong — they answer different questions, and the raw row is what lets you explain the gap.
How coarse should you aggregate?
Granularity is the size of each group, on the same two axes: time (hour / day / week) and dimension (device → channel → total). Every step up merges groups and deletes distinctions. In the example, a daily mean hides the 14:00 bulk order among nine small ones; an hourly view shows a normal morning and one abnormal afternoon hour. Same data, different visibility. For the full treatment, see data granularity.
Decision rules — start from the question, not from the chart:
- If the question is "is the tail getting worse?" (slower checkouts, more large orders), aggregate per day but use P90, not the mean; a weekly average smooths away exactly the spikes you are hunting.
- If the question is "which channel gets the budget?", compare within device, or report both the device-level and the pooled numbers. Pooling across groups with different mixes can reverse the ranking — see the next section.
- If the question is "when did this happen?", keep hour granularity at least for incident windows; day granularity cannot answer it.
- If a summary raises a "why?", you need the rows behind it. Keep raw data exportable for as long as you might need to explain a number — after the detail is deleted, no re-aggregation can bring it back.
One-line rule: aggregate as coarse as the question allows, no coarser — and store one level finer than you report, so drill-down stays possible.
What aggregation can hide
Simpson's paradox: the pooled ranking reverses
Sample data: sessions and conversions for two channels, split by device.
| Channel | Desktop | Mobile | Pooled |
|---|---|---|---|
| A | 18/20 = 90% | 60/100 = 60% | 78/120 = 65% |
| B | 80/100 = 80% | 10/20 = 50% | 90/120 = 75% |
Channel A converts better on desktop (90% > 80%) and on mobile (60% > 50%) — yet loses the pooled comparison (65% < 75%). Nothing is miscalculated: A's traffic is concentrated in mobile, the harder segment, so its pooled rate is dragged toward mobile's lower base rate. This reversal is Simpson's paradox; the Stanford Encyclopedia of Philosophy entry documents the structure and real cases. The practical rule: compare at a granularity where the mix is comparable, or report both levels. See also Simpson's paradox.
NULLs change the denominator
Suppose two of the ten orders have no amount recorded (NULL). Under SQLite semantics: count(*) = 10, count(amount) = 8, and avg(amount) divides the sum of the eight known amounts by 8 — not by 10. A report that says "10 orders, average $X" may silently be averaging only 8. When counts and averages disagree across two dashboards, check NULL handling before blaming the collection pipeline.
Outliers own the mean
Already visible above: one $900 row takes the mean from 28.33 to 115.5. Sums and means are outlier-dominated; medians are outlier-robust but tail-blind. Report mean and median together whenever they can diverge, and add P90 when the tail itself is the question. A sharp gap between them is a signal to drill into the raw rows — not to pick whichever number looks better.
Does aggregation make data anonymous?
No — not by itself. Aggregation removes row-level detail and usually lowers re-identification risk, which is why statistical agencies publish tables rather than records. But NIST SP 800-188 (De-Identifying Government Datasets, 2023) states in §4.3.8 that aggregation alone provides no formal privacy guarantee.
The report's example: a school publishes an aggregate table of reading scores by grade; a student transfers in; the school publishes the table again. Subtracting the two releases can reveal the single transfer student's reading level — a group of one, recovered from two "anonymous" summaries. Two structural risks follow:
- Small cells. A cell counting 1–2 people is effectively a record about them. Statistical agencies apply a threshold rule (suppress cells below a minimum count), plus dominance and ambiguity rules for values concentrated in a few respondents.
- Differencing across releases. Overlapping tables published at different times or granularities can be subtracted to reconstruct small groups, even when every single table looks safe on its own.
Practical rule before sharing aggregates outside the team: check for small cells, check which earlier releases a recipient could diff against, and treat "it's aggregated" as a risk reduction — never as an anonymization guarantee.
How to work with aggregates in TapCub
Take the example above into TapCub: the same $900 bulk order, and the question "how do we report today without misleading anyone, and what happens once the rows are gone?"
- Pick the aggregation per question. In All-platform analytics › Behavior › Metrics & SQL, a saved metric is an event plus an aggregation, or a formula. For the purchase event, total gives 10, uniques gives 7, average per user gives 10 ÷ 7 ≈ 1.43 orders, and the property aggregations on the amount give sum 1,155, average 115.5, median 29 and the 90th percentile. Revenue per buyer is a formula,
PROPSUM ÷ UNIQUES= 1,155 ÷ 7 = 165. One event, several metrics: give each one a name that says which question it answers before it goes on a dashboard. - Look at the rows behind the number. The reports are aggregates. To find the $900 order at 14:00, open the SQL tab in All-platform analytics › Behavior › Behavior analysis and run one
SELECTon thev_eventsview for that day; results return up to 50,000 rows. - Check the pooled comparison before moving budget. In the same builder, break the conversion metric down by channel and then device (up to three layers) and set granularity to hour or day. That rebuilds the Simpson table above, A pooled 65% vs B 75% while A wins inside each device, and it shows whether the 14:00 hour is the outlier. TapCub does not run A/B tests; this comparison measures, it does not assign traffic.
- Keep the detail you may need to explain. Detail rows older than the retention period are deleted. Daily aggregates contain no personal data and are not deleted on that timer, so the day-level trend survives while the rows behind it do not. If you may need the raw rows later, create an async export job in All-platform analytics › Data › Data export before retention runs out. TapCub is not a data warehouse; the export is how the rows reach yours.
That is the working rule of this article in product form: aggregate for the question you are answering, and keep a path back to the rows.
Sources
- PostgreSQL Global Development Group, Aggregate Functions — count/sum/avg semantics; percentile_cont vs percentile_disc.
- SQLite, Built-in Aggregate Functions — count(*) vs count(X); avg over non-NULL inputs.
- NIST SP 800-188, De-Identifying Government Datasets (September 2023), §4.3.8 — aggregation is not a formal privacy guarantee.
- Stanford Encyclopedia of Philosophy, Simpson's Paradox.
Was this explanation clear?
We use your answers to fix and expand this page.
Couldn’t send. Please try again in a minute.
Thanks for the feedback. We’ll use it to improve this page.You’ve already sent feedback on this page. Thanks.

Data Analyst
Explores traffic sources, conversion funnels, and retention, using clear metric definitions and concrete examples to explain what the numbers mean.