Actian X 12.0 | Aggregate Functions

OpenSQL Reference Guide > OpenSQL Reference Guide > Elements of OpenSQL Statements > Operations > Aggregate Functions

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: 08/14/2024