Summarizing with SUM( ) and AVG( )

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:

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.

Категории