Specifications

+------------+-------------+
| customerid | avg(amount) |
+------------+-------------+
| 1 | 49.990002 |
| 2 | 74.980003 |
| 3 | 47.485002 |
+------------+-------------+
One thing to note when using grouping and aggregate functions: In ANSI SQL, if you use an
aggregate function or GROUP BY clause, the only things that can appear in your SELECT clause
are the aggregate function(s) and the columns named in the GROUP BY clause. Also, if you want
to use a column in a GROUP BY clause, it must be listed in the SELECT clause.
MySQL actually gives you a bit more leeway here. It supports an extended syntax, which
enables you to leave items out of the SELECT clause if you don’t actually want them.
In addition to grouping and aggregating data, we can actually test the result of an aggregate
using a HAVING clause. This comes straight after the GROUP BY clause and is like a WHERE that
applies only to groups and aggregates.
To extend our previous example, if we want to know which customers have an average order
total of more than $50, we can use the following query:
select customerid, avg(amount)
from orders
group by customerid
having avg(amount) > 50;
Note that the HAVING clause applies to the groups. This query will return the following output:
+------------+-------------+
| customerid | avg(amount) |
+------------+-------------+
| 2 | 74.980003 |
+------------+-------------+
Choosing Which Rows to Return
One clause of the SELECT statement that can be particularly useful in Web applications is the
LIMIT clause. This is used to specify which rows from the output should be returned. It takes
two parameters: the row number from which to start and the number of rows to return.
This query illustrates the use of LIMIT:
select name
from customers
limit 2, 3;
Using MySQL
P
ART II
222
12 7842 CH09 3/6/01 3:36 PM Page 222