Group and Aggregate a TSV
A GROUP BY without a database. Pick the column that defines the groups, pick what to do with the rest, and get one row per group back. Every result includes a count column alongside your chosen aggregate, because the count is what tells you whether a suspicious average is real or is one row masquerading as a trend.
How to use
- Paste or drop the file — the first row must be the header.
- Name the grouping column in By. Comma-separate for a multi-level group:
region,monthgives one row per region-and-month combination. - Choose the aggregate and the column it applies to. Count rows needs no column.
- Sort by the result to see the top groups first — that's usually the question you actually had.
How the numeric aggregates treat non-numbers
Sum, average, min, max, and median ignore any value they can't read as a number — blanks, N/A, —, text. They don't fail and they don't treat those as zero. This is deliberate but has a consequence worth internalising: an average over a column that's half empty is the average of the half that has values, not of every row in the group. The count column shows total rows in the group, so a large gap between the count and what you expected to be averaged is your signal. Values with thousands separators (1,234) are read as numbers; currency symbols are not, so strip those first with find and replace.
The non-numeric aggregates
Count distinct answers "how many different values does this group have" — how many unique products per order, how many distinct countries per region. Join all values and join distinct values concatenate with a semicolon, which turns a one-row-per-tag table into a one-row-per-item table with a tag list. That's the inverse of what split a column does, and the pair of them is how you move between the two shapes.
First and last take the value from the first or last row of the group in file order, not by any sort. If you want the first by date, sort by that date column first — sorting is stable, so the group order is preserved.
FAQ
Can I compute two aggregates at once?
Not in one pass. Run it twice — once for sum, once for average — and join the two results on the group column with TSV join. Since both outputs have identical group columns, the join is exact.
How is this different from a pivot table?
Group-by produces one row per group and keeps the result in a single column. Pivot spreads a second dimension across the columns, giving you a matrix. Use group-by for a ranked list, pivot for a cross-tab.
Are the groups case-sensitive?
Yes — UK and uk are two groups. Normalise first with find and replace in a column if the source data is inconsistent, and use value counts to spot the variants.
Why does my sum have a long decimal tail?
Floating-point arithmetic. Results are rounded to ten decimal places to suppress the worst of it, but summing money is best done on integer cents. Round numbers will tidy the output for display.
Privacy
100% client-side. No upload. See the privacy policy.