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