Aggregate and Numeric Functions - Learning MySQL and MariaDB - page 292

without a negative sign. Absolute values are also important for certain mathematical cal-
culations.
We'll try this function by using part of some examples from the previous section, where
we determined the total number of seconds each member took to identify birds. This time
we'll just calculate a total for all rows, not grouping by human_id :
SELECT
SUM( TIME_TO_SEC( TIMEDIFF(id_start, id_end) ) )
AS 'Total Seconds for All',
ABS( SUM( TIME_TO_SEC( TIMEDIFF(id_start, id_end) ) ) )
AS 'Absolute Total'
FROM bird_identification_tests;
+-----------------------+----------------+
| Total Seconds for All | Absolute Total |
+-----------------------+----------------+
| -1689 | 1689 |
+-----------------------+----------------+
There's not much to this function and example. The first field has a negative sign because
we put the id_start before the id_end within TIMEDIFF() .We could just reverse
the order, but there will be situations in which you won't know which value will be great-
er than the other. For this, you may need ABS() .
In other situations, you want to know whether a value is positive or negative. For this, you
can use the SIGN() function.It returns a value of 1 if the argument given results in a
positive number, -1 if it results in a negative number, and 0 if it's given a value of zero.
As an example, let's go back to our bird identification tests. Suppose we want a list of
birds that members identified in less time than the average. We calculated the minimum
average earlier in Calculating a Group of Values . We'll reuse part of that SQL statement,
but save the results to a user-defined variable and use that variable to compare each row in
bird_identification_tests so we can list only rows in which the time it took to
identify the bird was less than average. Set up that variable and test it by entering this on
your server:
SET @min_avg_time =
(SELECT MIN(avg_time) FROM
(SELECT AVG( TIME_TO_SEC( TIMEDIFF(id_end, id_start)))
AS 'avg_time'
FROM bird_identification_tests
GROUP BY human_id) AS average_times);
SELECT @min_avg_time;