Author: Written by Isaac Wilcox <[email protected]> on 2000-06-16
Aggregate functions compute a single result value from a set of input values. The special syntax considerations for aggregate functions are explained in Section 1.3.4. Consult the PostgreSQL Tutorial for additional introductory information.
Table 4-22. Aggregate Functions
| Function | Description | Notes |
|---|---|---|
| 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 values | The 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.