relational algebra aggregate functions
another type of request that cannot be expressed in the basic relational algebra is to specify mathematical aggregate functions on collections of values from the database
we can define an AGGREGATE FUNCTION operation, using the symbol
, to specify these types of requests as follows:
where <grouping attributes> is a list of attributes of the relation specified in 
, and <function list> is a list of (<function> <attribute>) pairs. in each such pair, <function> is one of the allowed functions--such as SUM, AVERAGE, MAXIMUM, MINIMUM, COUNT--and <attribute> is an attribute of the relation specified by . the resulting relation has the grouping attributes plus one attribute for each element in the function list that is the result of applying the function to the groups of tuples of where each group is identified by the grouping attributes. for example, to retrieve each department number, the number of employees in the department, and their average salary, while renaming the resulting attributes as indicated below, we write:
in this preceding example, we specified a list of attribute names--between parentheses in the rename operation--for the resulting relation . if no renaming is applied, then the attributes of the resulting relation that correspond to the function list will each be the concatenation of the function name with the attribute name in the form 
. for example,
.
if no grouping attributes are specified, the functions are applied to all the tuples in the relation, so the resulting relation has a single tuple only. for example
.
it is important to note that, in general, duplicates are not eliminated when an aggregate function is applied; this way, the normal interpretation of functions such as SUM and AVERAGE is computed. however, NULL values are not considered in the aggregation. it is worth emphasizing that the result of applying an aggregate function is a relation, not a scalar number--even if it has a single value. this makes the relational algebra a closed mathematical system.
[cite:@elmasri_db_2015 chapter 8.4.2 aggregate functions and grouping]
we can define an AGGREGATE FUNCTION operation, using the symbol
, to specify these types of requests as follows:
where <grouping attributes> is a list of attributes of the relation specified in , and <function list> is a list of (<function> <attribute>) pairs. in each such pair, <function> is one of the allowed functions--such as SUM, AVERAGE, MAXIMUM, MINIMUM, COUNT--and <attribute> is an attribute of the relation specified by . the resulting relation has the grouping attributes plus one attribute for each element in the function list that is the result of applying the function to the groups of tuples of where each group is identified by the grouping attributes. for example, to retrieve each department number, the number of employees in the department, and their average salary, while renaming the resulting attributes as indicated below, we write:
in this preceding example, we specified a list of attribute names--between parentheses in the rename operation--for the resulting relation . if no renaming is applied, then the attributes of the resulting relation that correspond to the function list will each be the concatenation of the function name with the attribute name in the form . for example,
.
if no grouping attributes are specified, the functions are applied to all the tuples in the relation, so the resulting relation has a single tuple only. for example
.
it is important to note that, in general, duplicates are not eliminated when an aggregate function is applied; this way, the normal interpretation of functions such as SUM and AVERAGE is computed. however, NULL values are not considered in the aggregation. it is worth emphasizing that the result of applying an aggregate function is a relation, not a scalar number--even if it has a single value. this makes the relational algebra a closed mathematical system.
[cite:@elmasri_db_2015 chapter 8.4.2 aggregate functions and grouping]