7.4 Summarizing with SUM( ) and AVG( )
7.4.1 Problem
You
need to add up a set of numbers or find their average.
7.4.2 Solution
Use the SUM( ) or AVG( )
functions.
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 non-numeric 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.
|
