Group rows and add them up

Pick the columns that define a group and the numbers you want summarized, and every group comes back as one row. Three measures at a time, each with its own aggregate, so sum of revenue and count distinct of customer is one pass rather than two. Totals are computed from the raw values, and every figure prints at the precision your source column was written at.

Three measures at once, because that is how the question is asked

Nobody has ever wanted only the sum. The question is always shaped like "revenue and order count by region", or "total hours and number of distinct people per project", or "how much, how many, and what was the biggest one". One measure at a time means running the tool three times and pasting the columns back together in a spreadsheet, at which point you have introduced the exact class of alignment error the tool was supposed to prevent.

So this page takes three, each with its own column and its own aggregate. Sum amount, count distinct customer_id, and take the max of amount, and you get one table with all three beside the group they belong to.

The eight aggregates are sum, average, count, count distinct, minimum, maximum, median and the 90th percentile. The last two are the ones that are usually missing and usually wanted: an average response time is a number that hides its own outliers, and a median beside a p90 tells you what most requests looked like and what the slow tail looked like, which is the shape of the thing rather than one summary of it.

Worked example, and the rounding nobody else gets right

Ten orders, grouped by region, summing amount. Every price in the source has two decimal places:

order_id,customer_id,order_date,region,product,quantity,amount
1001,C001,2024-01-05,East,Widget,3,120.50
1003,C001,2024-01-11,East,Thing,2,240.00
1007,C005,2024-03-03,East,Widget,1,199.99
1010,C001,2024-04-02,East,Doohickey,2,76.25
...

Out comes:

region,sum_amount,distinct_customer_id
East,636.74,3
North,60.85,1
South,64.00,1
West,530.60,3
Total,1292.19,6

Look at 636.74 and 64.00. Both have two decimal places, because the source column had two decimal places. That sounds obvious and it is not what most tools do. Floating point addition of 120.50 + 240.00 + 199.99 + 76.25 genuinely produces 636.7399999999999 in every language that uses doubles, and a tool that prints the accumulator writes that into the file you download. We found a pivot on a competitor's site producing 153237.7999999998 in a CSV, and our own pivot had the same bug before this audit.

The fix is to look at how the source column was written and round to that. One decimal place in, one decimal place out. Two in, two out. Whole numbers in, whole numbers out, with no .00 appearing where the data never had one. Derived figures that genuinely carry more precision than their inputs, which is averages, medians and percentiles, get two extra places and then have the padding trimmed off again.

Grouping by more than one column

Tick two columns and a group is a combination: East + Widget is one row, East + Gadget is another. This is the difference between "revenue by region" and "revenue by region and product", and it is a checkbox rather than a dropdown because the second question is at least as common as the first.

The grouping columns come out on the left in the order you ticked them, and the result is sorted by them so the same file always produces the same file. That matters more than it sounds: a summary you regenerate every month should differ only where the data differed, not because a hash map iterated in a different order.

If what you actually want is one dimension down the side and another across the top, that is a crosstab rather than a group-by, and the crosstab page is the same arithmetic laid out as a grid.

The result filter runs on the aggregate

There are two completely different things people mean by filtering a summary, and mixing them up produces wrong numbers that look right.

Filtering the rows happens before the grouping. "Total revenue by region, counting only orders over $100" throws away the small orders and then adds up what is left. That is a job for the filter tool first, or a filter step in a pipeline.

Filtering the result happens after. "Only show me the regions whose total is over $500" counts every order, and then hides the groups that came out small. The totals for the groups you keep are still correct, because nothing was excluded from them. In SQL this is the difference between WHERE and HAVING, and it is one of the genuinely confusing bits of SQL.

The filter on this page is the second kind. Set it to at least 500 on the East and West example above and you get two rows, both with their full totals intact. The summary strip tells you how many groups were filtered out, so a result that came back nearly empty is explained rather than mysterious.

Counting things, which is three different questions

Count is the number of rows in the group, whatever is in them. Blank cells count. This answers "how many orders".
Count distinct is the number of different values in the chosen column. This answers "how many customers", which is a completely different number from "how many orders" the moment anybody orders twice, and getting them confused is how a report ends up claiming three times the customer base.
Neither of them needs a numeric column. Count distinct on a text column is the useful case: distinct products per region, distinct owners per stage, distinct error messages per service.

Values that cannot be read as a number are left out of sum, average, min, max, median and p90 rather than being treated as zero, and the count of what was skipped is reported above the table. Treating N/A as zero silently drags every average down, and it is the kind of error nobody finds because the output looks completely normal. Blank cells are excluded without comment, since a blank is not a failed number, it is an absent one.

The total row, and why it is not a sum of the column above it

The bottom row is computed from the raw values, not from the numbers displayed above it. For a sum those two happen to be the same. For everything else they are not, and the difference matters.

The average of four regional averages is not the average of the file, unless every region has exactly the same number of rows. A region with two orders and a region with two hundred would count equally, which is nonsense. Same for the median: there is no way to combine four medians into the median of the whole. Same for count distinct, where a customer who ordered in two regions is one customer in the total and two if you add the column up.

So the total row keeps its own accumulator over every row of the file and reports that. It will sometimes disagree with adding up the column by hand, and when it does, it is the column that is misleading rather than the total that is wrong. Turn it off if the summary is going somewhere that will try to sum it again.

Frequently Asked Questions

Why does my sum have thirteen decimal places somewhere else and two here?

Because floating point addition of decimals does not land on exact values, and most tools print the accumulator directly. Adding 120.50, 240.00, 199.99 and 76.25 genuinely produces 636.7399999999999 in JavaScript, Python and every other language using doubles. This page looks at how many decimal places the source column was written with and rounds the result to match, so a column of prices sums to a price.

What is the difference between count and count distinct?

Count is how many rows are in the group. Count distinct is how many different values that column holds within the group. If one customer placed four orders, count says 4 and count distinct on customer_id says 1. Reports that mean to say "customers" and actually say "orders" are the commonest arithmetic error in this whole area, which is why both are offered rather than just the easy one.

Can I group by two columns at once?

Yes, tick as many as you like. A group is then the combination of all of them, so grouping by region and product gives you one row per region-and-product pair. The grouping columns appear on the left in the order you ticked them, and the result is sorted by them so the same input always produces the same output.

Does the result filter change my totals?

No. It runs after the grouping, so it hides whole groups without excluding any rows from the groups that remain. That is SQL's HAVING rather than WHERE. If you want to exclude rows before they are counted, filter the file first with the sort and filter tool, or put a filter step ahead of the group step in a pipeline.

What happens to text in a column I am summing?

It is left out of the arithmetic and counted. Above the result you get a line saying how many values could not be read as numbers, so a column that is half text does not quietly produce a total based on the other half. Blank cells are excluded silently, since a blank is a missing value rather than a broken one.

Why is the total row different from adding up the column myself?

For sums it will not be. For averages, medians, percentiles and count distinct it will, because those cannot be combined from group results. The average of four group averages ignores that the groups are different sizes; the total row is the average of every row in the file, which is the number you actually meant. Count distinct in the total counts a value once even if it appears in three groups.

Can I get a median or a percentile?

Yes, both. Median and the 90th percentile are in the aggregate list alongside the usual five. Both interpolate between order statistics the way DuckDB and pandas do, so a median of an even-length group falls between the two middle values rather than picking one of them.

Summarize your file without a spreadsheet

Three measures at once, eight aggregates, and totals that are right rather than merely plausible.

Back to the group-by tool