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.
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.
COUNT_BIG
COUNT_BIG
Result type: INTEGER8
Returns count of non-null occurrences.
For example, count the number of employees allocated to a department:
SELECT COUNT(dept)
FROM employee;
GROUPING
GROUPING
Result type: INTEGER
Returns an integer value based on its parameters.
It can be used to simplify a query that needs many GROUP BY levels by letting you more easily express row filtering conditions.
The GROUPING function can have multiple parameters, each of which must be a column or expression in the GROUP BY clause of an aggregate query. The parameters from right to left are represented in the function result by bits from right to left.
When CUBE, ROLLUP, or GROUPING SETS syntax is used, result rows from the query can be formed based on the grouping of rows according to different combinations of the grouping columns or expressions. If a result row is based on a grouping that includes a parameter of the grouping function, its bit position in the GROUPING() function result is 0, otherwise 1. This helps you determine which groupings each of the result rows comes from.
For example:
SELECT sno, pno, SUM(qty), GROUPING(sno, pno) FROM sp
GROUP BY CUBE(sno, pno)
produces results like:
s1 p1 25 0
s1 p2 30 0
...
s1 100 1
s2 50 1
...
p1 95 2
p2 10 2
...
1000 3
The numbers 0, 1, 2, and 3 are the results of the GROUPING() function. Rows with both sno and pno have a GROUPING() value of 0 because they result from the grouping of both sno and pno. Rows with only sno (superaggregate rows grouped only on sno) have a value of 1 because the rightmost parameter (pno) does not contribute to the grouping. Rows with only pno (superaggregates grouped only on pno) have a value of 2 because the next parameter (sno--in bit position 2) does not contribute. And the last row has a value of 3 because neither sno nor pno are involved in the grouping.
MAX
MAX
Result type: Same as argument
Returns maximum value.
MIN
MIN
Result type: Same as argument
Returns minimum value.
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).
SUM
SUM
Result type: INTEGER, FLOAT, MONEY, DATE (INTERVAL only)
Returns column total.
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).