Aggregate Functions

Aggregate functions compute a single result value from a set of input values. The special syntax considerations for aggregate functions are explained in the Section called Aggregate Expressions in Chapter 1.

Table 3-6. Aggregate Functions

FunctionDescriptionNotes
AVG(expression)the average (arithmetic mean) of all input values Finding the average value is available on the following data types: smallint, integer, bigint, real, double precision, numeric, interval. The result is of type numeric for any integer type input, double precision for floating point input, otherwise the same as the input data type.
COUNT(*)number of input valuesThe return value is of type integer.
COUNT(expression) Counts the input values for which the value of expression is not NULL.  
MAX(expression)the maximum value of expression across all input values Available for all numeric, string, and date/time types. The result has the same type as the input expression.
MIN(expression)the minimum value of expression across all input values Available for all numeric, string, and date/time types. The result has the same type as the input expression.
STDDEV(expression)the sample standard deviation of the input values Finding the standard deviation is available on the following data types: smallint, integer, bigint, real, double precision, numeric. The result is of type double precision for floating point input, otherwise numeric.
SUM(expression)sum of expression across all input values Summation is available on the following data types: smallint, integer, bigint, real, double precision, numeric, interval. The result is of type numeric for any integer type input, double precision for floating point input, otherwise the same as the input data type.
VARIANCE(expression)the sample variance of the input values The variance is the square of the standard deviation. The supported data types are the same.

It should be noted that except for COUNT, these functions return NULL when no rows are selected. In particular, SUM of no rows returns NULL, not zero as one might expect. NULL values in MAX(), SUM(), and AVG() are ignored.

Aggregates can be combined with a WHERE clause to provide more complex results.