SUM, AVG, MIN, MAX

COUNT told you how many. These four tell you how much. They work the same way: read every row the query would otherwise return, and produce a single number from a single column. Between them and COUNT you can answer most of what anyone means by "summarise this file".

Four functions, one shape

SUM(revenue) adds the column up. AVG(revenue) is the arithmetic mean. MIN and MAX take the smallest and largest. All four ignore nulls, which is worth stating plainly because it drives the most common wrong number in reporting.

Consider an average over a hundred rows where thirty are blank. AVG divides by seventy, not a hundred, because the thirty unknowns are excluded rather than treated as zeros. Sometimes that is exactly right: an average satisfaction score should not be dragged down by tickets nobody rated. Sometimes it is exactly wrong: an average daily spend across a month should count the days you spent nothing. SQL cannot tell which you meant. When zeros are what you want, say so with AVG(COALESCE(spend, 0)).

MIN and MAX are not only for numbers. On a date column they give you the first and last date in the file, which is the fastest way to check the period an extract actually covers. On text they give you the alphabetically first and last value, which is less often useful but occasionally tells you about a stray value at one end of the range.

Several aggregates at once

Nothing stops you putting them side by side:

SELECT
  COUNT(*)     AS orders,
  SUM(revenue) AS total_revenue,
  AVG(units)   AS avg_units,
  MIN(date)    AS first_day,
  MAX(date)    AS last_day
FROM csv

One row out, five numbers on it, one pass over the file. This is the query I run first on any new dataset, and I would encourage you to build the habit of writing it before you write anything clever.

What you cannot do is mix an aggregate and a plain column in the same select list without a GROUP BY. SELECT region, SUM(revenue) FROM csv is a question with no answer: there are four regions and only one sum. Most databases reject it; a couple pick a region at random, which is worse. Making that query legal is the entire point of lesson 8.

Rounding, and why money is awkward

Average a column of prices and you will get something like 1938.2280555555556. That is a floating point number doing its honest best, and it is not a figure to put in a report. ROUND(AVG(revenue), 2) gives you two decimal places.

A deeper point sits underneath. Summing a floating point column can produce answers that differ in the last bit depending on the order the rows were added, which is why parallel engines can return 211859.65999999997 one day and 211859.66 the next. Nothing is broken. Financial systems avoid the whole question by storing money as an exact DECIMAL type, or as an integer number of cents. That is why the exercise grader in this course compares numbers with a small tolerance rather than demanding bit-for-bit equality.

Aggregate over a slice

A WHERE clause under an aggregate restricts which rows get aggregated, exactly as you would hope. SELECT SUM(revenue) FROM csv WHERE channel = 'Retail' totals the retail rows only. Filter first, aggregate second: that is the order the engine works in, and it is why WHERE is written above the aggregate rather than around it.

A last piece of trivia that occasionally matters: SUM of no rows is NULL, not zero. Filter down to a category that does not exist and you get one row containing nothing, rather than a zero or an empty result. If a dashboard needs a number there, wrap it: COALESCE(SUM(revenue), 0). COUNT, by contrast, returns a genuine zero, because counting nothing is a well-defined activity.

What to look up next

Look up "median in SQL". There is no MEDIAN in the standard, every database solves it differently, and DuckDB gives you both median() and quantile_cont(). On skewed data such as response times, the median tells you something the average actively hides.