AVG Function

The AVG function returns the average of all values in the specified column or expression.

You can apply the AVG function only to number columns. The query in the following example finds the average price of a helmet:

SELECT AVG(unit_price) FROM stock WHERE stock_num = 110;

The return value is calculated by dividing the sum of unit_price values by the cardinality of the qualifying rows.

If you use the DISTINCT or UNIQUE keyword as the first argument, the average (meaning the mean) is calculated from only the distinct values in the specified column or expression. In the following example, only one instance of any duplicate values is included when the sum and the cardinality are calculated:

SELECT AVG(DISTINCT unit_price) FROM stock WHERE stock_num = 110;

If the data set included no duplicate values, both examples above return the same AVG value.

NULL values are ignored unless every value in the column or expression is NULL. If every value is NULL, the AVG function returns NULL for that column or expression.