COUNT, SUM, AVG, MIN and MAX

COUNT counts rows or non-NULL values. SUM adds up a numeric column. AVG divides the sum by the number of non-NULL values. MIN and MAX return the smallest and largest value. All five ignore NULLs except COUNT(*), which counts rows including those with NULLs in the other columns.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 120.00),
  (2, 1, 340.50),
  (3, 2,  75.25),
  (4, 2, 610.00);

SELECT COUNT(*) AS row_count,
       SUM(amount) AS total,
       AVG(amount) AS average,
       MIN(amount) AS smallest,
       MAX(amount) AS largest
FROM orders;

-- The same aggregates over a set with no matching rows.
SELECT COUNT(*) AS count_empty,
       SUM(amount) AS sum_empty,
       AVG(amount) AS avg_empty,
       MIN(amount) AS min_empty
FROM orders
WHERE customer_id = 999;

The first query returns four rows counted, a total of 1145.75, an average of 286.4375, a smallest of 75.25 and a largest of 610.00. The second returns a count of 0 and NULL for every other aggregate — and that asymmetry is worth knowing. COUNT on an empty set is 0 because zero is a meaningful count, while SUM, AVG, MIN and MAX return NULL because there is no value to add up or compare.

That distinction shows up in application code. A report that divides by COUNT assumes it is never zero; if the group is empty, COUNT gives 0 and the division raises an error in most engines. A report that divides by SUM silently produces NULL instead, which usually serialises as a null field in JSON and shows up as a blank in the UI rather than as a crash.

One more trap worth naming: SUM and AVG over a column of integers in SQLite return an integer, so SUM of three 10s gives 30, not 30.0. If you need a decimal result, cast the column to REAL or NUMERIC first. And remember that COUNT(DISTINCT column) is a different beast from COUNT(column) — it counts distinct non-NULL values and needs its own index to be efficient.

Group by the customer, then put the aggregates in the projection. The grouping column has to be in the SELECT list as well, otherwise the output has no way to say which row belongs to whom.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 120.00),
  (2, 1, 340.50),
  (3, 1,  90.00),
  (4, 2,  75.25),
  (5, 2, 610.00),
  (6, 3,  40.00);

SELECT customer_id,
       COUNT(*) AS order_count,
       SUM(amount) AS total_spent,
       ROUND(AVG(amount), 2) AS average_order,
       MIN(amount) AS smallest_order,
       MAX(amount) AS largest_order
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

Three rows come back. Customer 1 has three orders totalling 550.50 with an average of 183.50, a smallest of 90.00 and a largest of 340.50. Customer 2 has two orders totalling 685.25, averaging 342.63, with a range of 75.25 to 610.00. Customer 3 has a single order of 40.00, so the smallest and the largest are both 40.00 and the average is 40.00 as well.

That last customer is the interesting case and it is worth drawing out. When a group has exactly one row, the minimum and the maximum are the same value, and the average equals that value too. If your report shows a suspicious-looking average of exactly one of the order amounts, this is why — and if the report is meant to show a distribution, you might want a separate order-count column to make it visible.

Two details about the query itself. ROUND is applied to AVG because an average of 183.5 can otherwise print as 183.49999999999997 with float arithmetic. And the ORDER BY on customer_id is there for stability; without it the row order is unspecified, and a report that paginates needs a deterministic order.

Because the two forms count different things. AVG(amount) divides the sum by the number of non-NULL values in that column. SUM(amount) / COUNT(*) divides by the number of rows in the group, including rows where the amount is NULL. When the column has no NULLs the two agree; when it has NULLs they do not, and the naive form is always wrong because it divides by a larger denominator.

Consider a group of three orders where one has a NULL amount. AVG(amount) averages the two real values, which is usually what the business means by "the average order value". SUM(amount) / COUNT(*) divides by three, so the total is silently spread across a phantom order and the average comes out a third too small.

The fix is to make the denominator match the numerator: use AVG(amount), or if you need to do the arithmetic yourself, use SUM(amount) / NULLIF(COUNT(amount), 0). The NULLIF guard matters because COUNT(amount) is 0 for a group where every amount is NULL, and dividing by zero is an error in SQLite and PostgreSQL.

Two related traps. If the column is a float, AVG can produce values like 183.49999999999997, so wrap it in ROUND for display. And if you compute a weighted average — average order value multiplied by order count, divided by total orders — you have to be explicit about whether rows with NULL should contribute a count of one or zero, because the two readings give different answers.

The general principle for an interview: whenever you mix an aggregate with a count, ask whether the count has the same NULL semantics as the aggregate. If they differ, you are dividing by the wrong thing.