Aggregate Functions

I l @ ve RuBoard

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 non NULL 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; 

I l @ ve RuBoard