Learning Mysql

Suppose you want to know how many times you’ve listened to tracks on popular albums. 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 inset 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 tracks, together with the number of tracks they contain. Here’s the query you’d use:
mysql> SELECT artist_name, album_name, COUNT) FROM

Download 4.24 Mb.

Share with your friends: