Aggregate Functions

PostgreSQL includes a number of aggregate functions. Generally, aggregate functions calculate a single return value for an entire range of supplied input values. This behavior operates in contrast to standard functions, which generally return an output value for each supplied input.

AVG

Description

The AVG function returns the average value of the supplied column or expression.

Input

AVG(col | expression) 

Example

Return the average salary from the payroll table:

SELECT AVG(salary) FROM payroll; 

This example shows the use of expressions contained in the AVG function. Specifically, it returns the average amount over $18,000 that employees earn (notice that the criteria provided restricts calculations being performed on anyone earning less than $18,000).

SELECT AVG(salary-18000) FROM payroll WHERE salary>18000; 

Notes

The AVG function will work on the following data types: smallint, integer, bigint, real, double precision, numeric, and interval.

Any integer value (that is, bigint, integer, and so on) returns an integer data type.

Any floating-point value returns a numeric data type.

Others, such as interval, are returned as their own data type.

COUNT

Description

The COUNT function counts the rows or expressions where a nonNULL value is returned.

Inputs

COUNT(*)—Count all rows.

COUNT(col | expression)—Count a specific column or expression.

Example

SELECT COUNT(*) AS Num_Active FROM payroll WHERE status="active"; 

MAX

Description

The MAX function returns the greatest value from a column or expression list that was passed to it.

Input

MAX(col | expression) 

Example

SELECT MAX(salary) FROM payroll; 

MIN

Description

The MIN function returns the smallest value from a column or expression list that was passed to it.

Input

MIN(col | expression) 

Example

SELECT MIN(salary) FROM payroll; 

STDDEV

Description

The STDDEV function returns the standard deviation of the supplied columns or expression list.

Input

STDDEV(col | stddev) 

Example

SELECT STDDEV(price) FROM stocks; 

Notes

The STDDEV function will work on the following data types: smallint, integer, bigint, real, double precision, and numeric.

SUM

Description

The SUM function returns the aggregate sum of all the column or expression values passed to it.

Input

SUM(col | expression) 

Example

SELECT SUM(salary) FROM payroll WHERE checkdate='06-01-2001'; 

Notes

The SUM function will work on the following data types: smallint, integer, bigint, real, double precision, numeric, and interval.

VARIANCE

Description

The VARIANCE function will return the squared value of the standard deviation from the supplied column or expression list.

Input

VARIANCE(col | expression) 

Example

SELECT VARIANCE(price) FROM stocks;