Text Size: Normal / Large

Home → Documentation → Manuals → PostgreSQL 7.2

This page in other versions: 9.2 / 9.1 / 9.0 / 8.4 | Unsupported versions: 9.3 / 8.3 / 8.2 / 8.1 / 8.0 / 7.4 / 7.3 / 7.2 / 7.1 / devel

PostgreSQL 7.2.8 Documentation
Prev	Chapter 4. Functions and Operators	Next

4.14. Aggregate Functions

Author: Written by Isaac Wilcox <isaac@azartmedia.com> 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.5. Consult the PostgreSQL Tutorial for additional introductory information.

Table 4-30. 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 `bigint`.
`count`(`expression`)	Counts the input values for which the value of `expression` is not NULL.	The return value is of type `bigint`.
`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 `bigint` for `smallint` or `integer` input, `numeric` for `bigint` 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 and result types are the same as for standard deviation.

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. COALESCE may be used to substitute zero for NULL when necessary.

Prev	Home	Next
Miscellaneous Functions	Up	Subquery Expressions

Comments

2004-12-13 15:46:50.379528

I don\'t know so much sql, but a first() aggregate makes no sense to me.
If your query is unordered (no ORDER BY), first() would be undefined (because the order of the tuples in the group would be undefined),
if it is ordered first() would be either max() or min(), yo you can use these.
The only case when it can be useful is when there is an ordering of the tuples in a group defined by a multifield key (and then an expression for maxing or mining would still be possible, just more inconvenient to write), or when the key field type cannot be maxed or mined or converted to a supported type for max and min.
I guess sql is meant to operate on sets, not lists, and so first() and last() are a bit unnatural.