MySQL Cookbook

Summarizing with SUM( ) and AVG( )

Problem

You need to add up 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 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)  |
  +-----------+
  | 248102973 |
  +-----------+

  (The value represents the population reported for April, 1990. 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 non-numeric ...