SQL Avg()
AVG calculates the arithmetic mean of non-NULL values. A mean can be useful, but it can also hide the shape of the data. Always say what one input row represents.
Understand the idea
For paid orders, each row contributes one order total. The mean therefore describes the average paid order, not the average line item or the average customer's lifetime spend. If you need a weighted mean, calculate the weighted sum and divide by the sum of weights instead.
Where to try it
Run this example in the SQL playground. The lesson link loads the shop tables and sample rows before this query. Select Run when you are ready. Every run starts fresh, so your changes do not affect the website or a previous run.
SQLite sample data · MySQL / MariaDB sample data · See the sample tables
A worked example
SELECT AVG(total_cents) AS average_paid_cents
FROM orders
WHERE status = 'paid';
Read the result
The result is 1825 cents because the two paid orders total 3650 cents. There are only two observations, so this sample is for learning the calculation rather than drawing business conclusions.
Try one change
Calculate the average over all four orders and explain why pending and cancelled rows change the meaning of the measure.