Aggregation and Grouping Operations

Aggregation and Grouping Operations
As well as simply retrieving certain tuples and attributes of one or more relations, we often
want to perform some form of summation or aggregation of data, similar to the totals at
the bottom of a report, or some form of grouping of data, similar to subtotals in a report.
These operations cannot be performed using the basic relational algebra operations considered
above. However, additional operations have been proposed, as we now discuss.
Aggregate operations
IAL(R) Applies the aggregate function list, AL, to the relation R to define a relation
over the aggregate list. AL contains one or more (<aggregate_function>,
<attribute>) pairs.
The main aggregate functions are:
n COUNT – returns the number of values in the associated attribute.
n SUM – returns the sum of the values in the associated attribute.
n AVG – returns the average of the values in the associated attribute.
n MIN – returns the smallest value in the associated attribute.
n MAX – returns the largest value in the associated attribute.
Example 4.12 Aggregate operations
(a) How many properties cost more than £350 per month to rent?
We can use the aggregate function COUNT to produce the relation R shown in Figure
4.13(a) as follows:
ρR(myCount) ℑ COUNT propertyNo (σrent > 350 (PropertyForRent))
(b) Find the minimum, maximum, and average staff salary.
We can use the aggregate functions, MIN, MAX, and AVERAGE, to produce the relation
R shown in Figure 4.13(b) as follows:
ρR(myMin, myMax, myAverage) ℑ MIN salary, MAX salary, AVERAGE salary (Staff)
Grouping operation
GAℑAL(R) Groups the tuples of relation R by the grouping attributes, GA, and then
applies the aggregate function list AL to define a new relation. AL contains
one or more (<aggregate_function>, <attribute>) pairs. The resulting relation
contains the grouping attributes, GA, along with the results of each of
the aggregate functions.
The general form of the grouping operation is as follows:
a1, a2, . . . , an ℑ <Ap ap>, <Aq aq>, . . . , <Az az> (R)
where R is any relation, a1, a2, . . . , an are attributes of R on which to group, ap, aq, . . . , az
are other attributes of R, and Ap, Aq, . . . , Az are aggregate functions. The tuples of R are partitioned
into groups such that:
n all tuples in a group have the same value for a1, a2, . . . , an;
n tuples in different groups have different values for a1, a2, . . . , an.
We illustrate the use of the grouping operation with the following example.
Example 4.13 Grouping operation
Find the number of staff working in each branch and the sum of their salaries.
We first need to group tuples according to the branch number, branchNo, and then use the
aggregate functions COUNT and SUM to produce the required relation. The relational
algebra expression is as follows:
ρR(branchNo, myCount, mySum) branchNo ℑ COUNT staffNo, SUM salary (Staff)
The resulting relation is shown in Figure 4.14.