Overview

Aggregate processing is one of the most useful operations in DBMS environments. It summarizes large amounts of data with an aggregated value, including:

• The minimum, maximum, sum, or average value of a column in a specified set of rows

• The count of rows that match a condition

• Other statistical functions

In SQL, aggregate processing is performed using the aggregation functions min(), max(), count(), sum(), and avg(), and group by and having clauses. The SQL language implements two aggregate processing types, vector aggregation and scalar aggregation. A select-project-join (SPJ) query illustrates these two types of aggregate processing:

select r1, s1
from r, s
where r2 = s2

In vector aggregation, the SPJ result set is grouped on the group by clause expressions, and then the select clause aggregation functions are applied to each group. The query produces one result row per group:

select r1, sum (s1)
from r, s
where r2 = s2
group by r1

In scalar aggregation, there is no group by clause and the entire SPJ result set is aggregated, as a single group, by the same select clause aggregate functions. The query produces a single result row:

select sum (s1)
from r, s
where r2 = s2