Database Reference
In-Depth Information
Because we need the total number of seconds for each test in order to add the values to-
gether to get to an average, weused TIME_TO_SEC() to convert the results from
TIMEDIFF() (e.g., to convert from 121, for 1 minute and 21 seconds, to 81 seconds).
We did this extra step just to see how these values come more easily together with SUM()
and to better understand the time functions in the following SQL statement:
SELECT CONCAT(name_first, SPACE(1), name_last)
AS 'Birdwatcher',
SUM(TIME_TO_SEC( TIMEDIFF(id_end, id_start) ) )
AS 'Total Seconds for Identifications'
FROM bird_identification_tests
JOIN humans USING(human_id)
JOIN rookery.birds USING(bird_id)
WHERE name_first = 'Ricky' AND name_last = 'Adams';
+-------------+-----------------------------------+
| Birdwatcher | Total Seconds for Identifications |
+-------------+-----------------------------------+
| Ricky Adams | 436 |
+-------------+-----------------------------------+
That gives us the correct number of seconds that Ricky Adams spent identifying three
birds. Notice that this is another aggregate function that doesn't require the GROUP BY
clause. Now let's change the SQL statement to calculate the average time (e.g., 426
seconds divided by 3 entries). To do this, we'll use an absurdly complex and inefficient
method. We'll create a subquery to get each value to calculate the average. You don't have
to enter this one. Just look itover:
SELECT Identifications, Seconds,
(Seconds / Identifications) AS 'Avg. Seconds/Identification'
FROM
( SELECT human_id, COUNT(*) AS 'Identifications'
FROM bird_identification_tests
JOIN humans USING(human_id)
JOIN rookery.birds USING(bird_id)
WHERE name_first = 'Ricky' AND name_last = 'Adams')
AS row_count
JOIN
( SELECT human_id, CONCAT(name_first, SPACE(1), name_last)
AS 'Birdwatcher',
SUM(TIME_TO_SEC(TIMEDIFF(id_end, id_start)))
AS 'Seconds'
FROM bird_identification_tests
JOIN humans USING(human_id)
JOIN rookery.birds USING(bird_id) )
Search WWH ::




Custom Search