Database Reference
In-Depth Information
| Aegithinidae | 20 |
| Aegothelidae | 21 |
| Alaudidae | 447 |
| ... | |
| NULL | 28891 |
+--------------------+-------------------+
The total is on the last line and is equal to the count we did in the first example of this sec-
tion. In the results here, the NULL value for the first field doesn't refer to rows that don't
have a value for
family_id
. Instead, this is the total line. MySQL just doesn't have a
value to put in that field as a label, so it uses NULL. We can tweak that, though, to give it
a label. While we're doing that, let's include counts by orders of birds. Enter the follow-
ing:
SELECT IFNULL( bird_orders.scientific_name, '') AS 'Bird Order',
IFNULL( bird_families.scientific_name, 'Total:') AS 'Bird Family',
COUNT(*) AS 'Number of Species'
FROM birds
JOIN bird_families USING(family_id)
JOIN bird_orders USING(order_id)
GROUP BY bird_orders.scientific_name, bird_families.scientific_name
WITH ROLLUP;
+---------------------+--------------------+-------------------+
| Bird Order | Bird Family | Number of Species |
+---------------------+--------------------+-------------------+
| Anseriformes | Anhimidae | 3 |
| Anseriformes | Total: | 3 |
| Apodiformes | Apodidae | 316 |
| Apodiformes | Hemiprocnidae | 16 |
| Apodiformes | Trochilidae | 809 |
| Apodiformes | Total: | 1141 |
| Caprimulgiformes | Aegothelidae | 21 |
| Caprimulgiformes | Caprimulgidae | 224 |
| Caprimulgiformes | Nyctibiidae | 17 |
| Caprimulgiformes | Podargidae | 26 |
| ... | | |
| | Total: | 28890 |
+---------------------+--------------------+-------------------+
Besides adding another field to get the number of birds within an order of birds, we used
the
IFNULL()
functionto wrap the fields for the bird order counts and family counts.
This function tells MySQL that if the value for the field will be NULL, it should be re-
placed with the value or string given — else it should return the count. Because the state-
