Aggregate and Numeric Functions - Learning MySQL and MariaDB - page 275

| COUNT(*) |
+----------+
| 9553 |
+----------+
That's the answer we got before. It just required us to usethe IS NOT NULL operator.
As useful as all of this may be, let's get some more interesting results. Let's count the
number of birds within each family of birds. To do that, we have to use the GROUP BY
clause. We'll enter the following to get a count of the number of birds in each family:
SELECT COUNT(*)
FROM birds
GROUP BY family_id;
+----------+
| COUNT(*) |
+----------+
| 5 |
| 6 |
| 248 |
| 119 |
| 168 |
| 39 |
| 223 |
| ... |
+----------+
227 rows in set (0.15 sec)
In this example, we told MySQL to GROUP BY the family_id . So it sorted the rows
by the family_id and counted the number of rows for each group. Because the results
here would take up 227 rows, I've removed some of the results to save space. This SQL
statement did what we asked, but it's not very useful or interesting. It would be better to
get the name of the bird families to go with these counts. To do this, we'll have to use a
JOIN to include the bird_families table. Here's how we would do that:
SELECT bird_families.scientific_name AS 'Bird Family',
COUNT(*) AS 'Number of Species'
FROM birds JOIN bird_families USING(family_id)
GROUP BY birds.family_id;
+--------------------+-------------------+
| Bird Family | Number of Species |
+--------------------+-------------------+
| Gaviidae | 6 |