The SQLite avg() function returns the average value of all non-NULL values within a group.
It accepts one argument, which is the value or group of values.
Example
Take the following table:
ProductId ProductName Price ---------- --------------------- ---------- 1 Blue Widgets (6 Pack) 389.45 2 Widget Holder 139.5 3 Widget Opener 89.27 4 Foobar Set 120.0
We can use the avg() function to get the average value from the Price column. Like this:
SELECT avg(Price) FROM Products;Result:
184.555
How a WHERE Clause Affects the Result
If your query uses a WHERE clause, the average will be calculated after the WHERE clause has taken affect. In other words, you can use a WHERE clause to ensure that the avg() function calculates just those rows you’re interested in.
Here’s what happens if I add a WHERE clause to the previous example.
SELECT avg(Price)
FROM Products
WHERE ProductName LIKE '%widget%';Result:
206.073333333333
Round the Result
The result is returned as a floating point value (unless it’s NULL). You can use the round() function to round the value as required.
SELECT round(avg(Price), 2)
FROM Products
WHERE ProductName LIKE '%widget%';Result:
206.07
Limiting the Results
The LIMIT clause doesn’t affect the calculation of the avg() function – only one row is returned with the avg() function anyway.
SELECT avg(Price)
FROM Products
LIMIT 1;Result:
184.555
That said, providing a limit of zero will result in nothing being returned.
Strings and BLOBs
String and BLOB values that do not look like numbers are interpreted as 0.
SELECT avg(ProductName) FROM Products;Result:
0.0
NULL Arguments
If there are no non-NULL inputs, the avg() function returns NULL.
SELECT avg(NULL);Result:
(That result is NULL).
However, if there is at least one non-NULL argument, then it won’t return NULL.
To demonstrate this, here I’ll insert a new row, but leave the Price column at NULL:
INSERT INTO Products (ProductId, ProductName)
VALUES (5, 'Red Widget');
SELECT * FROM Products;Result:
ProductId ProductName Price ---------- --------------------- ---------- 1 Blue Widgets (6 Pack) 389.45 2 Widget Holder 139.5 3 Widget Opener 89.27 4 Foobar Set 120.0 5 Red Widget
So the last row doesn’t have a price – the Price column is NULL.
Now when I use the avg() function, it still returns the average of all the non-NULL rows.
SELECT avg(Price) FROM Products;Result:
184.555
The DISTINCT Keyword
You can use the DISTINCT keyword to calculate the average of just the distinct values in the column. This has the effect of removing any duplicate values from the calculation.
See Tweak your Avg() Results in SQLite with the DISTINCT Keyword for an example.