Database Reference
In-Depth Information
ment calculates first the primary totals (i.e., the totals for each family of birds), and then
calculates the secondary totals (i.e., the totals for each order of birds), this works.
The results in the previous example aren't marvelous, but you can easily use this method
in conjunction with a script that will display these results on a web page. You can use an
API to check for a value of
Total:
in the second field and then adjust for that. You could
instead do these simple calculations in an API script, rather than have MySQL do them.
However, sometimes it's better to do calculations at the database system level. I have
found often that better SQL statements make for tighter and easier to maintain API scripts.
All right; enough of that. Let's move on to more aggregate functions, besides just count-
ing the number ofrows.
Calculating a Group of Values
ing fun tests members could do online to try their skills at identifying birds. Suppose we
want to tell the member how long it takes them on average to identify birds. A simple cal-
culation would be to get the total time elapsed (i.e., subtracting
id_end
from
id_start
) for each row and then adding those differences together to get the sum of all
rows. We would then divide that sum by the number of rows. To get the sum, we can use
the
SUM()
function.
Before we jump too far ahead, though, let's look at some entries for one of the members to
remember and know what to do. We'll usethe
TIMEDIFF()
function to determine the
difference between the time the test started and when it ended (covered in the section
Comparing Dates and Times
)
. Enter the following:
SELECT common_name AS 'Bird',
TIME_TO_SEC( TIMEDIFF(id_end, id_start) )
AS 'Seconds to Identify'
FROM bird_identification_tests
JOIN humans USING(human_id)
JOIN rookery.birds USING(bird_id)
WHERE name_first = 'Ricky' AND name_last = 'Adams';
+--------------------+---------------------+
| Bird | Seconds to Identify |
+--------------------+---------------------+
| Crested Shelduck | 81 |
| Moluccan Scrubfowl | 174 |
| Indian Pond-Heron | 181 |
+--------------------+---------------------+
