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

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.