This section is from the "Practical PostgreSQL" book, by John Worsley and Joshua Drake. Also available from Amazon: Practical PostgreSQL.
Table 4-16. Aggregate Functions
Function | Description | Support Type | Return Type |
---|---|---|---|
AVG ( expression ) | the average of all input values (arithmetic mean) | small int, integer, bigint, real, double precision, numeric, interval. | numeric for integer type inputs and double precision for floating point input (For all other data types, the result is the same as the input data type) |
COUNT (*) | gives the sum of the number of input values | type integer | |
COUNT ( expression ) | gives a sum of the number of the input values for which the value of expression is not NULL | ||
MAX ( expression ) | the maximum value of ( expression ) across all input values | all numeric, string, and date/time types | same type as the input expression |
MIN ( expression ) | the minimum value of ( expression ) across all input values | all numeric, string, and date/time types | same type as the input expression |
STDDEV ( expression ) | the sample standard deviation of the input values | smallint, integer, bigint, real, double precision, numeric. | double precision for floating point inputs, otherwise, numeric |
SUM ( expression ) | the sum of ( expression ) across all input values | smallint, integer, bigint, real, double precision, numeric, and interval. | numeric for any integer type input, double precision for bloating point input. (For all other data types, the output is the same as the input data type.) |
VARIANCE ( expression ) | this is a sample of the variance from the input values | the same as the data types for standard deviation |
The functions shown in Table 4-16 can be used for a wide variety of needs. If you need to find the average of the suggested prices for all of the books, then you can use the AVG function.
Example 4-46. Using the average() Function
SELECT AVG(sugg_price) FROM publish; avg -------- $12.61 (1 row)
These are the psql commands that displays information about functions:
Lists all aggregate commands supported by PostgreSQL.
Lists all functions and their aggregates.
Displays comments about a specific function or group of functions.
Lists all operators and their arguments.
 
Continue to: