Email article link 
MySQL Recipe of the Day
The following recipe is from the MySQL Cookbook, by Paul DuBois. All links in this recipe point to the online version of the book on the Safari Bookshelf. Buy it now, or read it online on the Safari Bookshelf. 
7.4. Summarizing with SUM( ) and AVG( )
7.4.3. 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 nonnumeric values to useful numeric forms. Suppose 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 result also will be in seconds; pass it to SEC_TO_TIME( ) should you wish the sum to be in 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  +++
7.4.4. 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.
View the past week's recipes: Today  Yesterday  3 days ago  4 days ago  5 days ago  A week ago