Databases Reference
In-Depth Information
Suppose you want to know how many times you've listened to tracks on popular al-
bums. You've decided to define an album as popular if you've listened to one or more
of its tracks at least five times. In the previous section, we tried an almost identical
query but without the popularity limitation. Here's the new query, with an additional
HAVING
clause that adds the constraint:
mysql>
SELECT artist_name, album_name, COUNT(*) FROM
-> artist INNER JOIN album USING (artist_id)
-> INNER JOIN track USING (artist_id, album_id)
-> INNER JOIN played USING (artist_id, album_id, track_id)
-> GROUP BY album.artist_id, album.album_id
-> HAVING COUNT(*) >= 5;
+-------------+----------------------------+----------+
| artist_name | album_name | COUNT(*) |
+-------------+----------------------------+----------+
| New Order | Retro - Miranda Sawyer POP | 8 |
+-------------+----------------------------+----------+
1 row in set (0.01 sec)
You can see there's only one album that meets the new criteria.
The
HAVING
clause must contain an expression or column that's listed in the
SELECT
clause. In this example, we've used
HAVING COUNT(*) >= 5
, and you can see that
COUNT(*)
is part of the
SELECT
clause. Typically, the expression in the
HAVING
clause uses
an aggregate function such as
COUNT( )
,
SUM( )
,
MIN( )
, or
MAX( )
. If you find yourself
wanting to write a
HAVING
clause that uses a column or expression that isn't in the
SELECT
clause, chances are you should be using a
WHERE
clause instead. The
HAVING
clause
is only for deciding how to form each group or cluster, not for choosing rows in the
output. We'll show you an example later that illustrates when not to use
HAVING
.
Let's try another example. Suppose you want a list of albums that have more than 10
tracks, together with the number of tracks they contain. Here's the query you'd use:
mysql>
SELECT artist_name, album_name, COUNT(*) FROM
-> artist INNER JOIN album USING (artist_id)
-> INNER JOIN track USING (artist_id, album_id)
-> GROUP BY artist.artist_id, album.album_id
-> HAVING COUNT(*) > 10;
+--------------------+------------------------------------------+----------+
| artist_name | album_name | COUNT(*) |
+--------------------+------------------------------------------+----------+
| New Order | Retro - John McCready FAN | 15 |
| New Order | Substance (Disc 2) | 12 |
| New Order | Retro - Miranda Sawyer POP | 14 |
| New Order | Retro - New Order / Bobby Gillespie LIVE | 15 |
| New Order | Substance 1987 (Disc 1) | 12 |
| Miles Davis | Live Around The World | 11 |
| The Rolling Stones | Exile On Main Street | 18 |
| The Stone Roses | Second Coming | 13 |
| Kylie Minogue | Light Years | 13 |
+--------------------+------------------------------------------+----------+
9 rows in set (0.00 sec)
