Database Reference
In-Depth Information
| Birdwatcher | Birds | Avg. Time |
+---------------+-------+-----------+
| Rusty Osborne | 2 | 01:59 |
| Lexi Hollar | 3 | 00:23 |
| Ricky Adams | 3 | 02:25 |
+---------------+-------+-----------+
This time we included more members — but limited the results to three — and include the
number of birds that each member identified. We also formatted the average time better.
We can see that Ricky Adams took much longer on average than Lexi Hollar. It may be
that Lexi is quicker or that Ricky was distracted when he was identifying birds.
Because we usedthe
LIMIT
clause, we can't determine the longest and quickest average
times from these results. To know that, we need to remove the
LIMIT
and then make the
SQL statement a subquery of another in which we will addan
ORDER BY
clause. Essen-
tially, the inner
SELECT
returns a list with each bird-watcher and their average time,
which the outer
SELECT
puts in the order we want:
SELECT Birdwatcher, avg_time AS 'Avg. Time'
FROM
(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) AS average_times
ORDER BY avg_time;
+-------------------+-----------+
| Birdwatcher | Avg. Time |
+-------------------+-----------+
| Lexi Hollar | 00:23 |
| Geoffrey Dyer | 00:25 |
| Katerina Smirnova | 00:48 |
| Rusty Osborne | 01:59 |
| Ricky Adams | 02:25 |
| Anahit Vanetsyan | 03:20 |
+-------------------+-----------+
Now we know that Lexi is the quickest and Anahit was the slowest. We had to use a sub-
query because you can't generally put a
GROUP BY
and an
ORDER BY
clause in the
same SQL statement. You have to do what we did here instead.
