Database Reference
In-Depth Information
If we don't want to know the names of who had the minimum average and who had the
maximum, we could use the
MAX()
and
MIN()
functions.Let's redo the previous SQL
statement to include those aggregate functions. Try this on your server:
SELECT MIN(avg_time) AS 'Minimum Avg. Time',
MAX(avg_time) AS 'Maximum Avg. Time'
FROM humans
JOIN
(SELECT human_id, COUNT(*) AS 'Birds',
TIME_FORMAT(
SEC_TO_TIME( AVG(
TIME_TO_SEC( TIMEDIFF(id_end, id_start)))
), '%i:%s' ) AS 'avg_time'
FROM bird_identification_tests
JOIN humans USING(human_id)
JOIN rookery.birds USING(bird_id)
GROUP BY human_id ) AS average_times;
+-------------------+-------------------+
| Minimum Avg. Time | Maximum Avg. Time |
+-------------------+-------------------+
| 00:23 | 03:20 |
+-------------------+-------------------+
Comparing these results to the previous ones, we can see that they are correct. If we want
to see the minimum and maximum time for each person, instead of the averages, we could
do this:
SELECT CONCAT(name_first, SPACE(1), name_last) AS 'Birdwatcher',
TIME_FORMAT(SEC_TO_TIME(
MIN(TIME_TO_SEC( TIMEDIFF(id_end, id_start)))
),%i:%s' ) AS 'Minimum Time',
TIME_FORMAT(SEC_TO_TIME(
MAX(TIME_TO_SEC( TIMEDIFF(id_end, id_start)))
), '%i:%s' ) AS 'Maximum Time'
FROM bird_identification_tests
JOIN humans USING(human_id)
JOIN rookery.birds USING(bird_id)
GROUP BY Birdwatcher;
+-------------------+--------------+--------------+
| Birdwatcher | Minimum Time | Maximum Time |
+-------------------+--------------+--------------+
| Anahit Vanetsyan | 00:20 | 08:48 |
| Geoffrey Dyer | 00:09 | 00:42 |
| Katerina Smirnova | 00:22 | 01:02 |
| Lexi Hollar | 00:11 | 00:39 |
