4. Elements of OpenSQL Statements : Functions : Aggregate Functions : Unary Aggregate Functions
 
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;
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.