|
<< Click to Display Table of Contents >> AVG function |
  
|
|
<< Click to Display Table of Contents >> AVG function |
|
Returns the average of the values in a specified column or an expression.
AVG([ALL] column_reference | DISTINCT column_reference)
Use AVG to calculate the average value for a numeric value (column, literal, parameter, or caculated values). The average is a total of the column values from all rows in the dataset divided by the number of rows. The dataset may be the entire table, a filtered dataset, or a logical group produced by a GROUP BY clause.
SELECT AVG(ItemsTotal)
FROM Orders
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 (values of 1, 2, 3, 0, NULL, and 0 result in an average of 1.2).
Constant values (such as literals and parameter values) can only be used with the AVG function if part of a calculation involving a table column. If AVG is applied to a calculation, the calculation is performed for each row, the result of the calculation added together, and the whole divided by the number of rows in the dataset.
SELECT AVG(ItemsTotal), AVG(ItemsTotal * 0.0825) AS AverageTax
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.
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.
SELECT C."Company", AVG(O."ItemsTotal") AS Average,
MAX(O."ItemsTotal") AS Biggest,
MIN(O."ItemsTotal") AS Smallest
FROM "Customer.db" C, "Orders.db" O
WHERE (C."CustNo" = O."CustNo")
GROUP BY C."Company"
ORDER BY C."Company"
SELECT statements. 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.
Note: the AVG function cannot be used with memo or BLOB columns.
