## With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

No credit card required

# 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, then apply aggregate functions to each set of values to compute its sum or average.

## Discussion

Recipe 12.13 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 |
+------------+--------+
| 2002-06-01 |   1.50 |
| 2002-06-02 |   0.00 |
| 2002-06-03 |   0.50 |
| 2002-06-04 |   0.00 |
| 2002-06-05 |   1.00 |
+------------+--------+```

To calculate cumulative rainfall for a given day, sum that day’s precipitation value with the values for all the previous days. For example, the cumulative rainfall as of `2002-06-03` is determined like this:

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

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

## With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

No credit card required