SQL AVG Function: Averages, NULL Values and Weighted Means

Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.

Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(40) NOT NULL,
  city VARCHAR(40),
  manager_id INTEGER
);
CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount DECIMAL(10,2),
  status VARCHAR(20) NOT NULL,
  created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
  (1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
  (3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
  (101,1,10,'paid','2026-01-01'),
  (102,1,30,'pending','2026-01-02'),
  (103,2,20,'paid','2026-01-02'),
  (104,3,NULL,'pending','2026-02-01'),
  (105,3,20,'paid','2026-02-02'),
  (106,NULL,5,'paid','2026-02-03');

An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.

The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.

The known amounts sum to eighty-five and there are five of them, so AVG(amount) is seventeen. The NULL amount is excluded from both the sum and denominator. Dividing eighty-five by all six sale records answers a different question and treats the missing amount as if it contributed zero. That can be a valid business policy only if it is explicitly intended, not an accidental consequence of choosing the wrong count.

AVG(COALESCE(amount,0)) includes one zero for the missing sale and divides by six. COALESCE(AVG(amount),0) instead leaves the normal known-value denominator intact and supplies a fallback only when the resulting average is NULL. These two expressions place the missing-value policy at different stages. The example table deliberately contains an unknown amount so you can observe the distinction before using averages in a real report.

Group averages should not normally be averaged again without weights. Customer 1’s known amounts average twenty, customer 2’s average twenty, and customer 3’s known average is twenty. Other datasets can have very different group sizes, making an unweighted average of customer averages diverge from the overall transaction average. To combine groups, retain each group’s sum and known-value count, then divide total sums by total counts. This is the weighted approach that preserves the original observation grain.

The result type depends on the input type and engine. Some engines can return an integer result for an average over integer input, while others promote the calculation. Cast to an appropriate decimal type if fractional results are required, and round for display after calculation rather than rounding every input indiscriminately. AVG ignores an empty filtered input and yields NULL because no mean exists. A zero denominator in a hand-written SUM/COUNT formula must also be guarded, typically with NULLIF.

AVG is shared by all four systems. SQL Server AVG over integer input returns an integer-type result, so cast when fractional precision is needed. SQLite’s AVG returns a floating-point approximation. Other engines’ precision and scale rules depend on the numeric input type; verify them for financial reporting.

Check the engine-specific syntax and version requirements in SQL Server AVG.

Calculate the average known amount for paid sales. Explain which four amounts enter the denominator.

Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.

SELECT AVG(amount) AS paid_mean
FROM sales WHERE status='paid';

paid_mean
13.75

Paid sales have amounts ten, twenty, twenty and five. Their sum is fifty-five and their count is four, so the mean is 13.75. The pending sale with a missing amount cannot affect either side of this paid-only calculation.

Compare AVG(amount) with AVG(COALESCE(amount,0)). The first is seventeen; the second uses six observations and is approximately 14.167. Then filter to the missing amount alone: AVG is NULL rather than zero. Explain how replacing a final unknown average differs from treating a missing input measurement as a known zero before the denominator is formed.