Database Reference
In-Depth Information
| Ricky Adams | 01:21 | 03:01 |
| Rusty Osborne | 01:50 | 02:08 |
+-------------------+--------------+--------------+
This shows an alphabetic list of members and each one's minimum and maximum time to
identify a bird. Essentially, once you group items by the bird-watcher, you can run aggreg-
ate functions such as
AVG()
and
MAX()
on them.We removed the field counting the
number of identifications they made.
We could play with this more to see which birds take the longest to identify and which
take the least amount of time. We could mark ones that are most difficult to identify for
more advanced members. Some members may have a low average time if it were not for
one bird that was particularly difficult to identify. For those entries, we could use the ag-
gregate functions for more advanced statistical calculations to remove them,functions like
STDDEV()
and
VARIANCE()
. As a beginner, you probably won't need to know them.
Just know that they exist in case one day you do.
Before moving on, let's look at one more example using
MIN()
and
MAX()
, an example
that uses values other than time values. The
bird_sightings
table contains informa-
tion on birds that our members saw in the field. It includes the GPS coordinates where
each bird was seen: the
location_gps
column. This column contains two 11-digit
numbers: the latitude and the longitude on the globe. Because birds tend to migrate
between north and south, suppose we want to know the farthest north and south that birds
were seen. We coulduse
SUBSTRING()
to extract the latitude, the
MAX()
function to
determine which value is farthest north, and
MIN()
to determine which is the farthest
south. We would do this like so:
SELECT common_name AS 'Bird',
MAX(SUBSTRING(location_gps, 1, 11)) AS 'Furthest North',
MIN(SUBSTRING(location_gps, 1, 11)) AS 'Furthest South'
FROM birdwatchers.bird_sightings
JOIN rookery.birds USING(bird_id)
WHERE location_gps IS NOT NULL
GROUP BY bird_id LIMIT 3;
+-----------------+----------------+----------------+
| Bird | Furthest North | Furthest South |
+-----------------+----------------+----------------+
| Eskimo Curlew | 66.16051056 | 66.16051056 |
| Whimbrel | 30.29138551 | 30.29138551 |
| Eurasian Curlew | 51.70469364 | 42.69096856 |
+-----------------+----------------+----------------+
