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:

\da

Lists all aggregate commands supported by PostgreSQL.

\df

Lists all functions and their aggregates.

\dd

Displays comments about a specific function or group of functions.

\do

Lists all operators and their arguments.