Managing & Using MySQL, 2nd Edition

Aggregate Functions

As mentioned earlier, aggregate functions work just like standard functions except that they have a couple of extra routines. Those routines drive the aggregation for each group of data:

reset

This routine is called at the beginning of each group of data. It resets any local data used to aggregate the data so that each group starts with an empty aggregation.

add

This routine is called for each row of data within a group. The purpose of the function is to perform any data aggregation required for the group.

The order in which routines are called illustrates the differences between standard and aggregate functions:

init
  reset
      add
      add
      add
      ...
      main
  reset
      add
      add
      add
      ...
      main
   reset
      add
      add
      add
      ...
      main
  ...
deinit

While a standard function is comprised of a single loop, an aggregate function is a loop of loops. For a standard function, the main routine is called for each row of data, which corresponds directly with each row of the result set. For an aggregate function, the rows of the result set are made up of groups, in which each row represents a group of rows of data. The add routine is called for each row of data (except the first), while the main routine is called only once for each group (resulting in one row of the result set).

While the signatures of the routines called within a standard function do not differ for an aggregate function, their roles are somewhat different.

init

Unlike a standard function, the init function is almost always used for aggregate ...