Specifications

The most commonly used ones are listed in Table 9.3.
TABLE 9.3 Aggregate Functions in MySQL
Name Description
AVG(column) Average of values in the specified column.
COUNT(items) If you specify a column, this will give you the number of non-NULL
values in that column. If you add the word DISTINCT in front of the
column name, you will get a count of the distinct values in that col-
umn only. If you specify COUNT(*), you will get a row count regard-
less of
NULL values.
MIN(column) Minimum of values in the specified column.
MAX(column) Maximum of values in the specified column.
STD(column) Standard deviation of values in the specified column.
STDDEV(column) Same as STD(column).
SUM(column) Sum of values in the specified column.
Let’s look at some examples, beginning with the one mentioned earlier. We can calculate the
average total of an order like this:
select avg(amount)
from orders;
The output will be something like this:
+-------------+
| avg(amount) |
+-------------+
| 54.985002 |
+-------------+
In order to get more detailed information, we can use the GROUP BY clause. This enables us to
view the average order total by group—say, for example, by customer number. This will tell us
which of our customers place the biggest orders:
select customerid, avg(amount)
from orders
group by customerid;
When you use a GROUP BY clause with an aggregate function, it actually changes the behavior
of the function. Rather than giving an average of the order amounts across the table, this query
will give the average order amount for each customer (or, more specifically, for each
customerid):
Working with Your MySQL Database
C
HAPTER 9
9
W
ORKING WITH
YOUR MYSQL
DATABASE
221
12 7842 CH09 3/6/01 3:36 PM Page 221