Database Reference
In-Depth Information
Counting and Grouping Results
In many of ourexamples, we displayed only a few rows of data because the results could
potentially contain thousands of rows. Suppose we'd like to know how many are contained
in the table. We can do that by adding a
function
to the statement. In this case, we want the
COUNT()
function. Let's see how that would work:
SELECT COUNT(*) FROM birds;
+----------+
| COUNT(*) |
+----------+
| 28891 |
+----------+
We put an asterisk within the parentheses of thefunction to indicate that we want all of the
rows. We could put a column name instead of an asterisk to count only rows that have data.
Using a column prevents MySQL from counting rows that have a NULL value in that
column. But it will count rows that have a blank or empty value (i.e.,
''
).
It's nice to know how many rows are in the
birds
table, but suppose we'd like to break
apart that count. Let's use
COUNT()
to count the number of rows for a particular family of
birds, the
Pelecanidae
— those are Pelicans. Enter this SQL statement in the
mysql
client
on your server:
SELECT families.scientific_name AS 'Family',
COUNT(*) AS 'Number of Birds'
FROM birds, bird_families AS families
WHERE birds.family_id = families.family_id
AND families.scientific_name = 'Pelecanidae'
+-------------+-----------------+
| Family | Number of Birds |
+-------------+-----------------+
| Pelecanidae | 10 |
+-------------+-----------------+
As you can see, there are 10 bird species recorded for the
Pelecanidae
family in the
birds
table. In this example, we used the
WHERE
clause to limit the results to the
Pelecanidae
family. Suppose we want to know the number of birds for other bird families in the same
order to which Pelicans belong, to the order called
Pelecaniformes
. To do this, we'll add
the
bird_orders
table to the previous
SELECT
statement. Enter the following from the
mysql
client:
