How to Use awk to Sum Average and Count Columns the Safe Way

Totals over a column of numbers are the daily use of awk: disk usage, request sizes, durations, money. The code is short, and the care goes into the edges: headers, blank lines, non-numbers and empty input. This post covers them in order. It follows fields and printf. Examples use GNU awk 5.2 and a CSV of orders.

The sample

id,customer,amount
1,ada,120.50
2,grace,75.00
3,ada,30.25
4,linus,n/a
5,grace,200.00

Sum, count and average

awk -F, 'NR > 1 && $3 ~ /^[0-9.]+$/ { s += $3; n++ }
  END { if (n) printf "rows=%d sum=%.2f avg=%.2f\n", n, s, s / n; else print "no data" }' orders.csv

rows=4 sum=425.75 avg=106.44

The condition skips the header (NR > 1) and any row whose third field is not a plain number, so the n/a row is excluded from both sum and count. Dividing by NR would give the wrong average, since it counts the header and the bad row. The if (n) guard avoids a division by zero on an empty file, which would stop awk with an error.

Minimum and maximum

awk -F, 'NR > 1 && $3 ~ /^[0-9.]+$/ {
  if (!seen++ || $3 + 0 < min) min = $3 + 0
  if (!seen_max++ || $3 + 0 > max) max = $3 + 0
} END { print min, max }' orders.csv

30.25 200

Initializing from the first row avoids the trap of starting min at zero, which beats every positive number. The +0 forces a numeric comparison; without it, strings like "30.25" and "200" compare as text, and "200" sorts before "30.25".

Group by a key

awk -F, 'NR > 1 && $3 ~ /^[0-9.]+$/ { sum[$2] += $3; cnt[$2]++ }
  END { for (c in sum) printf "%-8s %8.2f %d\n", c, sum[c], cnt[c] }' orders.csv | sort -k2,2nr

grace      275.00 2
ada        150.75 2

The array index is the customer, and each row adds to that customer's total. The order of the for (c in sum) loop is not defined, so pipe the output through sort for stable output; gawk can sort inside with PROCINFO["sorted_in"], which other awks lack.

Sizes and units

du -k /var/log/* 2>/dev/null | awk '{ s += $1 } END { printf "%.1f MB\n", s / 1024 }'
ls -l | awk 'NR > 1 { s += $5 } END { print s }'

Awk handles large numbers as floating point, exactly up to 2 to the power 53 in most builds, which is more than enough for byte counts but not for 64-bit IDs. For integers beyond that, use bc or Python. Printing with %d may truncate very large values, and print s may switch to scientific notation; use printf "%.0f".

Where this does not apply

CSV with quoted commas breaks the field split; use a CSV-aware tool as in awk and CSV or running SQL on CSV files. For medians and percentiles, awk must keep all values, and sort plus awk, or a statistics tool, is clearer. The one-line sum is also available as paste -sd+ | bc, which is slower but readable.

How awk treats numbers and text

Awk has no separate integer type, and it decides at run time whether a value is a number or a string. A field that looks like a number is compared and added as a number, and one that does not counts as zero in arithmetic, silently. So a header line of text adds zero to a sum and increments the count, which makes an average wrong by one. Skip the header with NR > 1 as the pattern, and check that the column holds only numbers if the data is not clean. A trailing carriage return from a file with Windows line endings turns the last field into text that still converts to a number in most implementations, though it breaks string comparisons and key lookups, so strip it with sub(/\r$/, "") in a first rule if the data came from Windows. Thousands separators and currency symbols are text, and must be removed with gsub before arithmetic.

Floating-point arithmetic has the usual limits. Sums of decimal fractions pick up tiny errors, so money is better summed in the smallest unit as integers, and printed with printf and an explicit format.

Guards and edge cases

The average is the sum divided by the count, and an empty input makes the count zero. Division by zero is a fatal error in awk, so guard it with if (n > 0) in the END block. For minimum and maximum, initialize from the first record instead of from zero, since a minimum starting at zero never rises above it and a maximum starting at zero is wrong for negative data. Use a flag or check NR == 1. Locale can interfere: in a locale that uses a comma as the decimal mark, some awk implementations read and print numbers differently, and setting LC_ALL=C for the command gives predictable behavior. Output of print uses OFMT for non-integer numbers, which defaults to six significant digits, so a large sum may print rounded. Use printf "%.2f" when the digits matter.

Reporting by group and speed on large files

To total by a key, use an array indexed by the key: sum[$1] += $3, then loop in END to print each key and its total. The order of a for ... in loop is unspecified, so pipe the output through sort when the order matters, or in GNU awk set PROCINFO["sorted_in"]. Awk reads the file once, so it handles files much larger than memory, as long as the number of distinct keys fits. A key with high cardinality, such as a request ID, makes the array grow with the data and can exhaust memory. For tens of gigabytes with few keys, awk is hard to beat, and for data that needs joins, sorting or window functions, loading it into SQLite or a similar tool is easier to get right.

A complete small report

Suppose a file has the columns host, status and bytes. A single program can print, for each status, the number of requests, the total bytes and the average, followed by an overall line, and the pieces are all arrays and counters. Two arrays keyed on the status hold the count and the sum, updated on each record. The END block loops over the keys and prints one formatted line each, then the totals. Piping the output through sort orders the lines, and a printf with fixed widths aligns the columns so the result is readable in a terminal. Run it first on a small sample whose answer you can compute by hand, to confirm the logic, and then on the real data.

Convert units at the end and not on each line. Summing bytes as integers and dividing by 1024 or 1048576 when printing avoids rounding errors and keeps the arithmetic simple.

Mind negative numbers and blanks. An empty field adds zero and still counts as a record, which lowers the average, so either skip empty values with a pattern such as $3 != "" or count them separately and report them. A field that contains text because of a malformed line adds zero as well, and a quick check with $3 !~ /^-?[0-9.]+$/ printing the offending line to standard error shows how many records the average ignores.

Finally, state the result with its basis. A line saying "average 183.4 over 12,041 records, 3 skipped" tells the reader what the number means and what it leaves out, which matters more than the number itself.

See the gawk manual on arrays and POSIX awk expressions.