Database Reference
In-Depth Information
AS second_count
USING(human_id);
+-----------------+---------+-----------------------------+
| Identifications | Seconds | Avg. Seconds/Identification |
+-----------------+---------+-----------------------------+
| 3 | 436 | 145.3333 |
+-----------------+---------+-----------------------------+
That was a lot of work for something that should be simple — and it can be. Let's change
that touse
AVG()
:
SELECT CONCAT(name_first, SPACE(1), name_last)
AS 'Birdwatcher',
AVG( TIME_TO_SEC( TIMEDIFF(id_end, id_start)) )
AS 'Avg. Seconds per Identification'
FROM bird_identification_tests
JOIN humans USING(human_id)
JOIN rookery.birds USING(bird_id)
WHERE name_first = 'Ricky' AND name_last = 'Adams';
+-------------+---------------------------------+
| Birdwatcher | Avg. Seconds per Identification |
+-------------+---------------------------------+
| Ricky Adams | 145.3333 |
+-------------+---------------------------------+
That was much easier, and without any subqueries. If we remove the
WHERE
clause, we
would get the average time for all of the members. Let's do that and change the formatting
of the time to minutes and seconds, not just the average of total seconds. We'lluse
SEC_TO_TIME()
to do that, reversing the results of
TIME_TO_SEC()
now that we've
calculated the average. Enter this on your server:
SELECT CONCAT(name_first, SPACE(1), name_last)
AS 'Birdwatcher',
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 LIMIT 3;
+---------------+-------+-----------+
