022/Text processing

Total a column grouped by another one in a single pass

2026.09.15·004 min·macOS · Debian · Alpine · Shell

$ awk '{n[$7]++; bytes[$7]+=$10} END {for (u in n) printf "%12.0f %6d %s\n", bytes[u], n[u], u}' access.log | sort -rn | head

anatomy

awk

Not a command with flags but a small language with a fixed execution model: read a line, split it into fields, test it against each pattern, run the block attached to the patterns that match. Everything you write between the quotes is a program, which is why the usual advice to pipe `grep` into `cut` into `paste` collapses into one invocation once you know the syntax.

{ ... }

A block with no pattern in front of it, so it runs on every line. The pattern slot is where `/ERROR/` or `$9 == 500` or `NR > 1` would go, and an empty slot means always. The mirror of this is a pattern with no block, which defaults to printing the line, and that is the entire explanation for why `awk '$9 == 500' access.log` works.

The seventh whitespace-separated field, which in the nginx and Apache combined log format is the request path. `$0` is the whole line and `NF` is the count, so `$NF` is the last field regardless of width. Fields are split on runs of whitespace by default, not on single spaces, so ragged column alignment costs you nothing.

n[$7]++

Every awk array is associative, and every index is a string even when it looks like a number. There is no declaration and no initialization: reading an element that was never set gives you an empty string that counts as zero in arithmetic, so `++` on a path you have never seen yields 1. This one line is the group-by.

bytes[$7]+=$10

A second array keyed by the same string, accumulating the response size. `$10` arrives as text and is coerced to a number by the arithmetic context, which also means a field like `-` (what these logs write when there is no body) converts to 0 rather than failing.

END

A block that runs once after the last line of the last file. The arrays are the only thing that survives the input loop, so `END` is where a counting program does its talking. Its counterpart `BEGIN` runs before the first line is read, which is where you would set `FS` or print a header.

for (u in n)

Walks the keys of the array, assigning each one to `u`. The order is whatever the implementation's hash table produces, and it is specified nowhere, so two awks on two machines will give you the same numbers in a different sequence. That is the reason for the pipe into `sort` rather than any preference about formatting.

printf "%12.0f %6d %s\n"

Fixed field widths to keep the columns aligned, and no newline unless you write one, unlike `print`. The byte total prints with `%.0f` instead of `%d` on purpose: mawk clamps `%d` at 2147483647, and an access log crosses two gigabytes without trying.

sort -rn

Numeric descending on the first field, which is why the byte total is printed first. Putting your sort key in column one is the cheapest habit in shell work: it means every consumer downstream gets a plain `sort -rn` instead of a `-k` argument you have to recount every time the format changes.

head

The top ten lines. On a log with thousands of distinct paths the interesting part is always the head of the distribution, and awk has already done the reduction, so this is discarding rows rather than doing work.

Sample output

     2403263      3 /api/reports
     1768420      2 /assets/app.js
       20144      1 /assets/logo.png
       14539      3 /api/users
         312      1 /api/login
          72      4 /api/health

Three requests to /api/reports moved 2.4 MB, which is 800 KB per response and the reason the egress bill looks the way it does. Compare it against /api/health, which was hit more often than anything else and cost 72 bytes in total. Request counts alone would have ranked these two backwards, and that inversion is the whole argument for totalling the bytes instead of counting the lines.

When you would reach for it

You have a log, a CSV, or any other column of text, and the question is not “which lines match” but “how much, grouped by what”. Bandwidth per endpoint, errors per service, build minutes per job, revenue per region: the shape is always the same, and the usual answer is to load the file somewhere that understands GROUP BY. This does it in one pass over the file, in memory that scales with the number of distinct keys rather than the number of lines, using a tool that is already on the machine.

Gotchas

mawk, which is the default awk on Debian and Ubuntu, prints any %d value above 2147483647 as exactly 2147483647. It does not warn and it does not wrap, it clamps, so a plausible-looking number is what you get back. Byte totals cross that line at two gigabytes. Use %.0f for anything you are summing, and reserve %d for counts you know are small. gawk prints the full value, so the same pipeline can be correct on the workstation where you wrote it and quietly wrong on the Debian box where it runs nightly.
Field numbers are a promise about the log format, not about the log. The combined format puts the path at $7 only because the timestamp in brackets counts as two fields, and one proxy that logs an extra header or one request line containing a space shifts everything to the right for that line alone. Run awk '{print NF}' access.log | sort | uniq -c first: more than one answer means your field numbers are wrong somewhere in the file.
The array holds one entry per distinct key, so grouping by a path with query strings in it can turn a million-line log into a million-element array and an out-of-memory kill. Normalize the key before you count it: sub(/\?.*/, "", $7) strips everything from the first question mark onward. Be aware that assigning to a field rebuilds $0 from the fields using OFS, so if you print the whole line later it comes back with single-space separators.
for (u in n) returns keys in hash order, and the hash differs between gawk, mawk and the BSD awk on macOS. Never present that order as meaningful, and never diff it between two machines. gawk can sort internally with PROCINFO["sorted_in"] = "@val_num_desc" set inside the END block, but that is a GNU extension and the external sort is portable.

Variants

$ awk -F, 'NR>1 {u[$2]+=$4; r[$2]+=$5} END {for (k in u) printf "%-10s %6d %10.2f\n", k, u[k], r[k]}' sales.csv | sort -k3 -nr

The same program against a CSV. -F, changes the field separator and NR>1 is a pattern that skips the header row

$ awk '{n[$9,$7]++} END {for (k in n) {split(k, p, SUBSEP); printf "%6d %4s %s\n", n[k], p[1], p[2]}}' access.log | sort -rn

Group by two columns at once. n[$9,$7] joins status and path with SUBSEP, an unprintable character awk reserves for this, and split takes the key back apart

$ zcat -f access.log access.log.*.gz | awk '{n[$7]++; bytes[$7]+=$10} END {for (u in n) printf "%12.0f %6d %s\n", bytes[u], n[u], u}' | sort -rn | head

A month of rotated logs as one table. -f tells zcat to pass uncompressed files through untouched, so the current log and its gzipped ancestors go into the same arrays

lineage

awk was written at Bell Labs in 1977 by Alfred Aho, Peter Weinberger and Brian Kernighan, and the name is nothing more than their three initials. It shipped to the world with Unix Version 7 in 1979, sold as a tool for one-line data reports: sum this column, print that field. Users took it further than the authors expected and started writing real programs in it, which by Aho's own telling came as a surprise to all three of them. The 1985 rewrite, the one usually called nawk, answered that by adding user-defined functions, dynamic regular expressions and multiple input streams through `getline`, and the 1988 book `The AWK Programming Language` documented the result. Then Perl arrived and absorbed the audience, because Larry Wall wanted the same conveniences without switching between awk and sed and shell. What is left is three living implementations with slightly different personalities: the original, still maintained by Kernighan himself, who added UTF-8 and a `--csv` option in 2023 at the age of 81; gawk, the GNU version that Arnold Robbins has shepherded since the late 1980s and that carries most of the extensions; and mawk, written by Mike Brennan in the early 1990s for speed, which is the plain `awk` you get on Debian and Ubuntu.

> every log file is a table that nobody indexed_