This section is from the "Practical PostgreSQL" book, by John Worsley and Joshua Drake. Also available from Amazon: Practical PostgreSQL.
An aggregate expression represents the application of an aggregate function across the rows selected by a query. An aggregate function takes in multiple inputs and returns a single output value, such as the sum or average of the inputs. The possible syntaxes for an aggregate function are as follows:
This function call invokes the aggregate across all input rows for which the given expression yields a non-NULL value. NOTE: An aggregate function may or may not ignore a NULL value, depending on its definition, but the standard aggregate functions are set to ignore NULLs.
This function call is in essence the same as the first syntax because it specifies ALL as the default.
This invokes the aggregate function for all distinct (non-repeating) non-NULL values of the expression found in the input rows.
Here, the aggregate is invoked once for each input row, regardless if the value is NULL or non-NULL because there is no input value specified. This is generally used for the count() function.
Aggregate Syntax | |
---|---|
Aggregate_name is a previously defined aggregate, and expression is any expression that does not contain an aggregate expression. |
 
Continue to: