Returns the average of the values in a specified column or an expression.
Syntax:
AVG([ALL] column_reference | DISTINCT column_reference)
As an aggregate function, AVG performs its calculation aggregating values in the same column(s) across all rows in a dataset. The dataset may be the entire table, a filtered dataset, or a logical group produced by a GROUP BY clause. Column values of zero are included in the averaging, so values of 1, 2, 3, 0, 0, and 0 result in an average of 1. NULL column values are not counted in the calculation.
Example:
SELECT AVG(itemstotal) FROM orders
ALL returns the average for all rows. When DISTINCT is not specified, ALL is the implied default.
DISTINCT ignores duplicate values when averaging values in the specified column.
AVG returns the average of values in a column or the average of a calculation using a column performed for each row (a calculated field).
Example:
SELECT AVG(itemstotal), AVG(itemstotal * 0.0825) AS AverageTax FROM orders
When used with a GROUP BY clause, AVG calculates one value for each group. This value is the aggregation of the specified column for all rows in each group. The statement below aggregates the average value for the order totals column in the ORDERS table, producing a subtotal for each company in the COMPANY table.
Example:
SELECT C.company, AVG(O.itemstotal) AS Average,
MAX(O.itemstotal) AS Biggest, MIN(O.itemstotal) AS Smallest
FROM customer C, order O
WHERE (C.custno = O.custno)
GROUP BY C.company
ORDER BY C.company
AVG operates only on numeric values. To use AVG on non-numeric values, first use the CAST function to convert the column to a numeric type.
The AVG function cannot be used with memo or BLOB columns.