Database: SELECT: Summary Functions

Summary functions (also known as aggregate or column functions) produce values from all the cells in a column. SQL1 defines the following:

Function	Result
SUM( {expr \| DISTINCT col} )	The sum of the values in the column.
AVG( {expr \| DISTINCT col} )	The average value in the column.
MIN(expr)	The minimum value in the column.
MAX(expr)	The maximum value in the column.
COUNT(DISTINCT col)	Counts all non-NULL values in a column.
COUNT(*)	Counts number of rows, regardless of NULL values.

Misc Notes

There will be only one row of output, unless you are using GROUP BY.
Eg, SELECT AVG(cost) ... will produce only one cell on one line, the average of all non-NULL cost cells.
Unless you are using GROUP BY, you may not mix non-summary expressions with summary expressions in the SELECT clause. If you specify any non-summary fields in the SELECT clause, they must also appear in a GROUP BY clause.
A summary function can not contain other summary functions in its argument; only expressions involving column names.
NULL values are ignored by all summary functions except COUNT(*).
SQL-92 allows an optional DISTINCT and general expressions in all of the summary functions (except COUNT(*) which just returns the number of rows).

Problem: Compute the average GPA for all students.
This requires only the Students table from the University Database.

/* Compute average GPA for all students */
SELECT 'Average GPA', AVG(stu_gpa)
    FROM Students;