Basic Aggregate Functions
A basic aggregate function returns a single value by aggregating mulitple rows of data.
The syntax of a basic aggregate function is as follows:
function_name ([DISTINCT | ALL] expr)
where:
function_name denotes an aggregate function.
expr denotes any expression that does not include an aggregate function reference (at any level of nesting).
DISTINCT eliminates duplicate values of expr within a set of input rows to the aggregation. ALL (the default) retains duplicate values of expr.
Null values of expr are ignored by the aggregate functions, with the exception of COUNT(*).
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;
Note: Aggregate functions used in OpenROAD can only be coded inside SQL statements.
Supported unary aggregate functions are as follows:
ANY
ANY
Result type: INTEGER
Returns 1 if any row in the table fulfills the where clause, or 0 if no rows fulfill the WHERE clause.
AVG
AVG
Result type: FLOAT, MONEY, DATE (INTERVAL only)
Returns the average (sum / count). The sum of the values must be within the range of the result data type.
COUNT
COUNT
Result type: INTEGER
Returns count of non-null occurrences.
COUNT(*)
COUNT(*)
Returns count of the number of rows in a result table, including rows that contain nulls.
For example, the following statement counts the number of employees in department 23:
SELECT COUNT(*)
FROM employee
WHERE dept = 23;
The asterisk (*) argument cannot be qualified with ALL or DISTINCT.
Because COUNT(*) counts rows rather than columns, it does not ignore nulls. Consider the following table:
NAME EXEMPTIONS
Smith 0
Jones 2
Tanghetti 4
Fong Null
Stevens Null
Running:
COUNT(exemptions)
returns the value of 3, whereas:
COUNT(*)
returns 5.
If the argument to the COUNT function evaluates to an empty set, COUNT returns zero, which is in contrast to other functions, which return a null.
MAX
MAX
Result type: Same as argument
Returns maximum value.
MIN
MIN
Result type: Same as argument
Returns minimum value.
SUM
SUM
Result type: INTEGER, FLOAT, MONEY, DATE (INTERVAL only)
Returns column total.
STDDEV_POP
STDDEV_POP
Result type: FLOAT
Computes the population form of the standard deviation (square root of the population variance of the group).
STDDEV_SAMP
STDDEV_SAMP
Result type: FLOAT
Computes the sample form of the standard deviation (square root of the sample variance of the group).
VAR_POP
VAR_POP
Result type: 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
VAR_SAMP
Result type: 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).