Advanced Querying - Learning MySQL - page 232

COUNT(artist_name) is the same as COUNT(*) or COUNT(artist_id) . Of course, you can
use a column alias for the COUNT( ) column.
Let's try another example. Suppose you want to know how many tracks are on each
album, along with the artist and album name. Here's the query:
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;
+---------------------------+-------------------------------------+----------+
| 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 | 15 |
| New Order | Power, Corruption & Lies | 8 |
| New Order | Substance 1987 (Disc 1) | 12 |
| New Order | Brotherhood | 10 |
| Nick Cave & The Bad Seeds | Let Love In | 10 |
| Miles Davis | Live Around The World | 11 |
| Miles Davis | In A Silent Way | 2 |
| The Rolling Stones | Exile On Main Street | 18 |
| The Stone Roses | Second Coming | 13 |
| Kylie Minogue | Light Years | 13 |
+---------------------------+-------------------------------------+----------+
13 rows in set (0.12 sec)
Before we discuss what's new, think about the general function of the query: it's an
INNER JOIN between artist , album , and track using the primary-key (identifier) col-
umns. Forgetting the aggregation for a moment, the output of this query is one row per
track.
The GROUP BY clause puts the rows together into clusters. In this query, we want the
tracks grouped together for each album by an artist. So, the GROUP BY clause uses
artist_id and album_id to do that. You can use the artist_id from any of the three
tables; artist.artist_id , album.artist_id , or track.artist_id are the same for this
purpose. It doesn't matter since the INNER JOIN makes sure they match anyway. The
same applies to album_id .
As in the previous example query, we're using the COUNT( ) function to tell us how many
rows are in each group. For example, you can see that COUNT(*) tells us that there are
15 tracks on New Order's Retro - John McReady FAN album. Again, it doesn't matter
what column or columns you count in the query: for example, COUNT(*) has the same
effect as COUNT(artist.artist_id) or COUNT(artist_name) .
Let's try another example. Say we want to know how many times we've listened to
tracks on each album. This query is a little trickier than the previous ones: we need to
think carefully about how to group the rows. We want rows for each album grouped
together—that is, we want to count the total number of times that any of the tracks on