MySQL Cookbook, 2nd Edition

Finding Cumulative Sums and Running Averages

Problem

You have a set of observations measured over time and want to compute the cumulative sum of the observations at each measurement point. Or you want to compute a running average at each point.

Solution

Use a self-join to produce the sets of successive observations at each measurement point, and then apply aggregate functions to each set of values to compute its sum or average.

Discussion

Calculating Successive-Row Differences illustrates how a self-join can produce relative values from absolute values. A self-join can do the opposite as well, producing cumulative values at each successive stage of a set of observations. The following table shows a set of rainfall measurements taken over a series of days. The values in each row show the observation date and the amount of precipitation in inches:

mysql>SELECT date, precip FROM rainfall ORDER BY date;
+------------+--------+
| date       | precip |
+------------+--------+
| 2006-06-01 |   1.50 |
| 2006-06-02 |   0.00 |
| 2006-06-03 |   0.50 |
| 2006-06-04 |   0.00 |
| 2006-06-05 |   1.00 |
+------------+--------+

To calculate cumulative rainfall for a given day, add that day’s precipitation value with the values for all the previous days. For example, determine the cumulative rainfall as of 2006-06-03 like this:

mysql>SELECT SUM(precip) FROM rainfall WHERE date <= '2006-06-03';
+-------------+
| SUM(precip) |
+-------------+
|        2.00 |
+-------------+

If you want the cumulative figures for all days that are ...