Database Reference
In-Depth Information
SELECT orders.scientific_name AS 'Order',
families.scientific_name AS 'Family',
COUNT(*) AS 'Number of Birds'
FROM birds, bird_families AS families, bird_orders AS orders
WHERE birds.family_id = families.family_id
AND families.order_id = orders.order_id
AND orders.scientific_name = 'Pelecaniformes';
+----------------+-------------+-----------------+
| Order | Family | Number of Birds |
+----------------+-------------+-----------------+
| Pelecaniformes | Pelecanidae | 224 |
+----------------+-------------+-----------------++
This tells us that there are 224 birds in the
birds
table that belong to
Pelecaniformes
.
There are five families in that order of birds, but it returned only the first family name
found. If we want to know the name of each family and the number of birds in each fam-
ily, we need to get MySQL to group the results. To do this, we have to tell it the column
by which to group. This iswhere the
GROUP BY
clause comes in. This clause tells
MySQL to group the results based on the columns given with the clause. Let's see how
that might look. Enter the following on your server:
SELECT orders.scientific_name AS 'Order',
families.scientific_name AS 'Family',
COUNT(*) AS 'Number of Birds'
FROM birds, bird_families AS families, bird_orders AS orders
WHERE birds.family_id = families.family_id
AND families.order_id = orders.order_id
AND orders.scientific_name = 'Pelecaniformes'
GROUP BY Family;
+----------------+-------------------+-----------------+
| Order | Family | Number of Birds |
+----------------+-------------------+-----------------+
| Pelecaniformes | Ardeidae | 157 |
| Pelecaniformes | Balaenicipitidae | 1 |
| Pelecaniformes | Pelecanidae | 10 |
| Pelecaniformes | Scopidae | 3 |
| Pelecaniformes | Threskiornithidae | 53 |
+----------------+-------------------+-----------------+
We gave the
GROUP BY
clause the
Family
alias, which is the
scientific_name
column from the
bird_families
table. MySQL returns one results set for all five fam-
ilies, for one
SELECT
statement.
