MySQL Cookbook

Recipe 8.3. Summarizing with SUM⁠(⁠ ⁠ ⁠) and AVG⁠(⁠ ⁠ ⁠)

Problem

You need to add a set of numbers or find their average.

Solution

Use the SUM⁠(⁠ ⁠ ⁠) or AVG⁠(⁠ ⁠ ⁠) functions.

Discussion

SUM⁠(⁠ ⁠ ⁠) and AVG⁠(⁠ ⁠ ⁠) produce the total and average (mean) of a set of values:

• What is the total amount of mail traffic and the average size of each message?

  mysql> SELECT -> SUM(size) AS 'total traffic', -> AVG(size) AS 'average message size' -> FROM mail; +---------------+----------------------+ | total traffic | average message size | +---------------+----------------------+ | 3798185 | 237386.5625 | +---------------+----------------------+

• How many miles did the drivers in the driver_log table travel? What was the average number of miles traveled per day?

  mysql> SELECT -> SUM(miles) AS 'total miles', -> AVG(miles) AS 'average miles/day' -> FROM driver_log; +-------------+-------------------+ | total miles | average miles/day | +-------------+-------------------+ | 2166 | 216.6000 | +-------------+-------------------+

• What is the total population of the United States?

  mysql> SELECT SUM(pop) FROM states; +-----------+ | SUM(pop) | +-----------+ | 293101881 | +-----------+

  The value represents the population reported for July 2004. The figure shown here differs from the U.S. population reported by the U.S. Census Bureau, because the states table doesn't contain a count for Washington, D.C.

SUM⁠(⁠ ⁠ ⁠) and AVG⁠(⁠ ⁠ ⁠) are strictly numeric functions, so they can't be used with strings or temporal values. On the other hand, sometimes you can convert nonnumeric values to useful numeric forms. Suppose that a table stores TIME values that represent elapsed time:

mysql> SELECT t1 FROM time_val; +----------+ | t1 | +----------+ | 15:00:00 | | 05:01:30 | | 12:30:20 | +----------+

To compute the total elapsed time, use TIME_TO_SEC⁠(⁠ ⁠ ⁠) to convert the values to seconds before summing them. The resulting sum also will be in seconds; pass it to SEC_TO_TIME⁠(⁠ ⁠ ⁠) to convert the sum back to TIME format:

mysql> SELECT SUM(TIME_TO_SEC(t1)) AS 'total seconds', -> SEC_TO_TIME(SUM(TIME_TO_SEC(t1))) AS 'total time' -> FROM time_val; +---------------+------------+ | total seconds | total time | +---------------+------------+ | 117110 | 32:31:50 | +---------------+------------+

See Also

The SUM⁠(⁠ ⁠ ⁠) and AVG⁠(⁠ ⁠ ⁠) functions are especially useful in applications that compute statistics. They're explored further in Chapter 13 along with STD⁠(⁠ ⁠ ⁠), a related function that calculates standard deviations.