SQL in a Nutshell, 3rd Edition

Name

AVG and SUM

The AVG function computes the average of values in a column or an expression, and SUM computes the sum. Both functions work with numeric values and ignore NULL values. Use the DISTINCT keyword to compute the average or sum of all distinct values in a column or expression.

ANSI SQL Standard Syntax

AVG( [ALL | DISTINCT] expression )
SUM( [ALL | DISTINCT] expression )

MySQL, PostgreSQL, and SQL Server

All of these platforms support the ANSI SQL syntax of AVG and SUM.

Oracle

Oracle supports the ANSI SQL syntax and the following analytic syntax:

AVG( [ALL | DISTINCT] expression ) OVER (window_clause)
SUM( [ALL | DISTINCT] expression ) OVER (window_clause)

For an explanation of the window_clause, see the “ANSI SQL Window Functions” section later in this chapter.

Examples

The following query computes average year-to-date sales for each type of book:

SELECT type, AVG( ytd_sales ) AS "average_ytd_sales" FROM titles GROUP BY type;

This query returns the sum of year-to-date sales for each type of book:

SELECT type, SUM( ytd_sales ) FROM titles GROUP BY type;