Function group: aggregate function
AVG() calculates the average of a set of numeric values. NULL values are ignored.
AVG ([ALL | DISTINCT] expression )
ALL
All values are taken into account, including duplicate values.
DISTINCT
Only unique values are taken into account. Duplicate values are ignored.
expression
Numeric expression (see section "Aggregate functions" for information on restrictions).
Result
If the set of values returned by expression is empty, the result or the result for this group is the NULL value.
Otherwise:
Without GROUP BY clause:
Returns the arithmetic average of all the values in the specified expression (see "Calculating aggregate functions").
With GROUP BY clause:
Returns the arithmetic average per group of all the values in the derived column for this group.
Data type: like expression with the following number of digits:
Integer or fixed-point number:
The total number of significant digits is 31, the number of digits to the right of the decimal point is 31-t+r. t and r are the total number of significant digits and the number of digits after the decimal point, respectively, in expression.
Floating-point number:
The total number of significant digits corresponds to 21 binary digits for REAL numbers and 53 for DOUBLE PRECISION.
Examples
SELECT without GROUP BY:
Calculate the average price of the services in the SERVICE table of the demonstration database (result: 783.33):
SELECT AVG(service_price) FROM service
If you enter a row in the table that contains the NULL value in the column service_price, the result does not change.
SELECT with GROUP BY:
The average price is calculated for each order number:
SELECT order_num, AVG(service_price) FROM service GROUP BY order_num order_num 200 1025 211 662.5 250 662.5