Database Reference
In-Depth Information
SELECT
to list rows where the
bird_id
is not included in
bird_families
. But this
is getting away from learning about aggregate functions. Let's assume that we've found
the rows with missing data and fixed them so that we can move on.
In the results for the last two examples, you may have noticed that the names of the bird
families are not listed alphabetically. That's because
GROUP BY
orders rows based on the
columns by which it is grouping (i.e.,
family_id
). If we want to order the results based
on the family name, the
scientific_name
in the
bird_families
table, we'd have
to change the
GROUP BY
clause to group by that column. Try entering this:
SELECT bird_families.scientific_name AS 'Bird Family',
COUNT(*) AS 'Number of Species'
FROM birds LEFT JOIN bird_families USING(family_id)
GROUP BY bird_families.scientific_name;
+--------------------+-------------------+
| Bird Family | Number of Species |
+--------------------+-------------------+
| Acanthisittidae | 9 |
| Acanthizidae | 238 |
| Accipitridae | 481 |
| Acrocephalidae | 122 |
| Aegithalidae | 49 |
| Aegithinidae | 20 |
| Aegothelidae | 21 |
| Alaudidae | 447 |
| ... | |
+--------------------+-------------------+
That's better. What would be nicer is if those results also showed the total number of birds
at the bottom. We can get that from a separate SQL statement, but to get the total in the
same results set, we would add
WITH ROLLUP
to the
GROUP BY
clause like so:
SELECT bird_families.scientific_name AS 'Bird Family',
COUNT(*) AS 'Number of Species'
FROM birds JOIN bird_families USING(family_id)
GROUP BY bird_families.scientific_name WITH ROLLUP;
+--------------------+-------------------+
| Bird Family | Number of Species |
+--------------------+-------------------+
| Acanthisittidae | 9 |
| Acanthizidae | 238 |
| Accipitridae | 481 |
| Acrocephalidae | 122 |
| Aegithalidae | 49 |
