Share this page

Unary Aggregate Functions

A unary aggregate function returns a single value based on the contents of a column. Aggregate functions are also called set functions.

Note: For OpenROAD users, aggregate functions used within OpenROAD can only be coded inside SQL statements.

The following example uses the sum aggregate function to calculate the total of salaries for employees in department 23:

select sum (employee.salary)

from employee

where employee.dept = 23;

from employee

where employee.dept = 23;

The following table lists SQL aggregate functions:

Name | Result Data Type | Description |

any | integer | Returns 1 if any row in the table fulfills the where clause, or 0 if no rows fulfill the where clause. |

avg | float, money, date (interval only) | Average (sum/count) The sum of the values must be within the range of the result data type. |

count | integer | Count of non-null occurrences |

max | same as argument | Maximum value |

min | same as argument | Minimum value |

sum | integer, float, money, date (interval only) | Column total |

stddev_pop | float | Compute the population form of the standard deviation (square root of the population variance of the group). |

stddev_samp | float | Computes the sample form of the standard deviation (square root of the sample variance of the group). |

var_pop | float | Computes the population form of the variance (sum of the squares of the difference of each argument value in the group from the mean of the values, divided by the count of the values). |

var_samp | float | Computes the sample form of the variance (sum of the squares of the difference of each argument value in the group from the mean of the values, divided by the count of the values minus 1). |

The general syntax of an aggregate function is as follows:

function_name ([distinct | all] expr)

where function_name denotes an aggregate function and expr denotes any expression that does not include an aggregate function reference (at any level of nesting).

To eliminate duplicate values, specify distinct. To retain duplicate values, specify all (this is the default.) Distinct is not meaningful with the functions min and max, because these functions return single values (and not a set of values).

Nulls are ignored by the aggregate functions, with the exception of count, as described in The Count Function and Nulls in this chapter.