Recipe 8.3. Summarizing with SUM( ) and AVG( )ProblemYou need to add a set of numbers or find their average. SolutionUse the SUM( ) or AVG( ) functions. DiscussionSUM( ) and AVG( ) produce the total and average (mean) of a set of values:
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 that 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 resulting sum also will be in seconds; pass it to SEC_TO_TIME( ) to convert the sum back to 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 | +---------------+------------+ See AlsoThe 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. |