MySQL Cookbook, 2nd Edition

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 ...