Database Reference
In-Depth Information
Aggregate Functions
Statistics can provideus with useful information about a database. If a database includes
information about the activities of an organization, we can determine some statistical in-
formation about those activities. If a database includes numeric values associated with
items an organization sells or tracks, statistics can provide us with information for making
decisions about those items.
In our
birdwatchers
database, we can use aggregate functions to understand the beha-
vior of our members in relation to our bird-watchers website, the events they attend, and
other activities. For our
rookery
database, we can ascertain some information about birds
using aggregate functions. That can be useful to our members related to searching for birds
in the wild, as well as their concerns for the well-being of birds. We can ascertain informa-
tion about where birds are seen in the wild by our members.
In this section, we will look at aggregate functions that will help us to determine this kind
of information. In order to aggregate data together to calculate statistical values of sorts, we
sometimes must usethe
GROUP BY
clause. Some of the aggregate functions, such as the
COUNT()
function we've used in earlier chapters for counting rows in a table, do not re-
quire this clause, at least under certain conditions. We'll start with
COUNT()
and then look
at functions for simple statistics, such as determining an average for a set of numbers.
Counting Values
One of the simplestcalculations we can do is to count. We learn it as children as an intro-
duction to mathematics. So let's start with counting, the
COUNT()
function.
Suppose we want to know how many birds are in the
birds
table. To do that, enter the fo-
lowing in
mysql
:
SELECT COUNT(*)
FROM birds;
+----------+
| COUNT(*) |
+----------+
| 28891 |
+----------+
Notice that we didn't have to include the
GROUP BY
clause for this simple SQL statement.
That's because we wanted MySQL to count all of the rows in the table. We didn't need
GROUP BY
because we didn't want it to separate the rows into separate groups — there's
just one group here. Notice also that we're giving
COUNT()
an asterisk as the argument.
