Databases Reference
In-Depth Information
| 3 | 2 |
| 1 | 5 |
| 4 | 1 |
| 1 | 6 |
| 5 | 1 |
| 6 | 1 |
| 1 | 7 |
+-----------+----------+
13 rows in set (0.01 sec)
But it still doesn't work! We get all 13 albums as answers. The reason is that an album
still matches itself because it occurs in both aliased tables.
To get the query to work, we need to make sure an album from one aliased table doesn't
match itself in the other aliased table. The way to do so is to specify that the albums in
each table shouldn't have the same artist:
mysql>
SELECT a1.artist_id, a2.album_id
-> FROM album AS a1, album AS a2
-> WHERE a1.album_name = a2.album_name
-> AND a1.artist_id != a2.artist_id;
Empty set (0.00 sec)
You can now see that there aren't two albums in the database with the same name but
by different artists. The additional
AND a1.artist_id != a2.artist_id
stops answers
from being reported where the artist is the same in both tables.
Table aliases are also useful in nested queries that use the
EXISTS
and
ON
clauses. We
show you examples later in this chapter when we introduce nested techniques.
Aggregating Data
Aggregate functions allow you to discover the properties of a group of rows. You use
them for purposes such as discovering how many rows there are in a table, how many
rows in a table share a property (such as having the same name or date of birth), finding
averages (such as the average temperature in November), or finding the maximum or
minimum values of rows that meet some condition (such as finding the coldest day in
August).
This section explains the
GROUP BY
and
HAVING
clauses, the two most commonly used
SQL statements for aggregation. But first, it explains the
DISTINCT
clause, which is used
to report unique results for the output of a query. When neither the
DISTINCT
nor the
GROUP BY
clause is specified, the returned raw data can still be processed using the
aggregate functions that we describe in this section.
The DISTINCT Clause
To begin our discussion on aggregate functions, we'll focus on the
DISTINCT
clause.
This isn't really an aggregate function, but more of a post-processing filter that allows
