AVG - SQL Function

AVG {Expression}

AVG returns the average of values in a specified field or expression.

Use AVG to calculate the average value for a numeric field. As an aggregate function, AVG performs its calculation aggregating values in the same field across all rows in a dataset. The dataset can be the entire table, a filtered dataset, or a logical group produced by a GROUP BY clause. Field values of zero are included in the averaging, so values of 1, 2, 3, 0, 0, 0 result in an average of 1. NULL field values are not counted in the calculation.

AVG function syntax has these named arguments:

For example, to calculate the average gross amount for all Invoices:

  SELECT AVG(GrossAmount) FROM INHEAD

When used with a GROUP BY clause, AVG calculates one value for each group. This value is the aggregation of the specified field for all rows in each group. The following example aggregates the average value for the GrossAmount field in the INHEAD table, producing a subtotal for each Customer in the ARCUST table:

  SELECT C.CustomerCode , AVG(H.GrossAmount) as Average, MAX(H.GrossAmount) as Biggest, MIN(H.GrossAmount) as Smallest

  FROM ARCUST C, INHEAD H

  WHERE C.CustomerCode = H.CustomerCode

  GROUP BY C.CustomerCode

  ORDER BY C.CustomerCode