Was this helpful?
Aggregate Functions
Aggregate functions include the following:
Unary
Binary
Count
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.
Binary Aggregate Functions
Ingres supports a variety of binary aggregate functions that perform a variety of regression and correlation analysis.
For all of the binary aggregate functions, the first argument is the independent variable and the second argument is the dependent variable.
The following table lists binary aggregate functions:
Name
Result Data Type
Description
regr_count
integer
Count of rows with non-null values for both dependent and independent variables.
covar_pop
float
Population covariance (sum of the products of the difference of the independent variable from its mean, times the difference of the dependent variable from its mean, divided by the number of rows).
covar_samp
float
Sample covariance (sum of the products of the difference of the independent variable from its mean, times the difference of the dependent variable from its mean, divided by the number of rows minus 1).
corr
float
Correlation coefficient (ratio of the population covariance divided by the product of the population standard deviation of the independent variable and the population standard deviation of the dependent variable).
regr_r2
float
Square of the correlation coefficient.
regr_slope
float
Slope of the least-squares-fit linear equation determined by the (independent variable, dependent variable) pairs.
regr_intercept
float
Y-intercept of the least-squares-fit linear equation determined by the (independent variable, dependent variable) pairs.
regr_sxx
float
Sum of the squares of the independent variable.
regr_syy
float
Sum of the squares of the dependent variable.
regr_sxy
float
Sum of the product of the independent variable and the dependent variable.
regr_avgx
float
Average of the independent variables.
regr_avgy
float
Average of the dependent variables.
Count(*) Function
Count can take the wildcard character, *, as an argument. This character is used to count the number of rows in a result table, including rows that contain nulls. For example, the statement:
select count(*)
    from employee
    where dept = 23;
counts the number of employees in department 23. The asterisk (*) argument cannot be qualified with all or distinct.
Because count(*) counts rows rather than columns, count(*) 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.
Except count, if the argument to an aggregate function evaluates to an empty set, the function returns a null. The count function returns a zero.
Aggregate Functions and Decimal Data
Given decimal arguments, aggregate functions (with the exception of count) return decimal results.
The following table explains how to determine the scale and precision of results returned for aggregates with decimal arguments:
Name
Precision of Result
Scale of Result
count
Not applicable
Not applicable
sum
39
Same as argument
avg
39
Scale of argument + 1 (to a maximum of 39)
max
Same as argument
Same as argument
min
Same as argument
Same as argument
Using Group By Clause with Aggregate Functions
The group by clause allows aggregate functions to be performed on subsets of the rows in the table. The subsets are defined by the group by clause. For example, the following statement selects rows from a table of political candidates, groups the rows by party, and returns the name of each party and the average funding for the candidates in that party.
select party, avg(funding)
    from candidates
    group by party;
Restrictions on the Use of Aggregate Functions
The following restrictions apply to the use of aggregate functions:
Aggregate functions cannot be nested.
Aggregate functions can only be used in select or having clauses.
If a select or having clause contains an aggregate function, columns not specified in the aggregate must be specified in the group by clause. For example:
select dept, avg(emp_age)
from employee
    group by dept;
The above select statement specifies two columns, dept and emp_age, but only emp_age is referenced by the aggregate function, avg. The dept column is specified in the group by clause.
Last modified date: 04/03/2024