Advanced Querying - Learning MySQL - page 233

the album have been played. So, we want to group together by artist and by album; we
don't want to group by track, since that'd split the tracks from each album into different
groups and tell us how many times we'd listened to each track. We also need a
four-way join between all four tables in the database, but that isn't hard to do using
the skills we've developed so far. 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)
-> INNER JOIN played USING (artist_id, album_id, track_id)
-> GROUP BY album.artist_id, album.album_id;
+-------------+----------------------------+----------+
| artist_name | album_name | COUNT(*) |
+-------------+----------------------------+----------+
| New Order | Retro - Miranda Sawyer POP | 8 |
| Miles Davis | Live Around The World | 3 |
+-------------+----------------------------+----------+
2 rows in set (0.11 sec)
You can see we've only listened to two albums: we've listened to one or more tracks
on New Order's Retro - Miranda Sawyer POP eight times, and one or more tracks on
the Miles Davis album Live Around The World three times. We don't know whether
it's the same track we've listened to multiple times, different tracks a few times, or many
tracks once: the GROUP BY clause hides the details. Again, we use COUNT(*) to do the
counting of rows in the groups, and you can see the INNER JOIN spread over lines 2 to
4 in the query.
Before we end this section, let's consider how results are displayed for a grouping op-
eration. The output rows are grouped together according to the GROUP BY clause, with
one row displayed for each group. You will typically not ask for fields that are collected
together in the grouping process, since the result will be meaningless. For example,
grouping the tracks by artist will produce:
mysql> SELECT * FROM track GROUP BY artist_id;
+----------+----------------------+-----------+----------+----------+
| track_id | track_name | artist_id | album_id | time |
+----------+----------------------+-----------+----------+----------+
| 0 | Elegia | 1 | 1 | 00:04:93 |
| 0 | Do You Love Me? | 2 | 1 | 00:05:95 |
| 0 | In A Silent Way | 3 | 1 | 00:01:81 |
| 0 | Rocks Off | 4 | 1 | 00:04:54 |
| 0 | Breaking Into Heaven | 5 | 1 | 00:11:37 |
| 0 | Spinning Around | 6 | 1 | 00:03:46 |
+----------+----------------------+-----------+----------+----------+
6 rows in set (0.01 sec)
Only the artist_id here is meaningful; the rest of the columns just contain the first-
listed entry from each group. To illustrate this point, “Elegia” is the first track that
would be listed for artist_id 1 if we hadn't performed any grouping:
mysql> SELECT * FROM track WHERE artist_id=1;
+----------+----------------------+-----------+----------+----------+