-> artist INNER JOIN album USING (artist_id)

| Miles Davis |
| Miles Davis |
| New Order |
| New Order |
| New Order |
| New Order |
| New Order |
| New Order |
| New Order |
| Nick Cave & The Bad Seeds |
| The Rolling Stones |
| The Stone Roses |
+---------------------------+
13 rows inset sec)
By running down the list, it’s easy to count off how many albums we’ve got by each artist one by Kylie Minogue, two by Miles Davis, seven by New Order, and so on.
The
GROUP
BY
clause can help automate this process by grouping the albums by artist;
we can then use the COUNT function to count off the number of albums in each group.
Here’s the query that does what we want:
mysql> SELECT artist_name, COUNT(artist_name) FROM
-> artist INNER JOIN album USING (artist_id)
-> GROUP BY artist_name;
+---------------------------+--------------------+
| artist_name | COUNT(artist_name) |
+---------------------------+--------------------+
| Kylie Minogue | 1 |
| Miles Davis | 2 |
| New Order | 7 |
| Nick Cave & The Bad Seeds | 1 |
| The Rolling Stones | 1 |
| The Stone Roses | 1 |
+---------------------------+--------------------+
6 rows inset sec)
You can see that the output we’ve asked for is artist_name, COUNT(artist_name)
, and this tells us exactly what we wanted to know. Notice also that we’ve used GROUP BY
artist_name to sort early for aggregation, rather than using ORDER BY artist_name later for presentation.
Let’s consider the query further. We’ll start with the
GROUP
BY
clause. This tells us how to put rows together into groups in this example, we’re telling MySQL that the way to group rows is by artist_name
. The result is that rows for artists with the same name form a cluster—that is, each distinct name becomes one group. Once the rows are grouped, they’re treated in the rest of the query as if they’re one row. So, for example,
when we write SELECT artist_name
, we get just one row for each group. This is exactly the same as DISTINCT, which performs the same function as grouping by a column name and then selecting that column for display. The COUNT function tells us about the properties of the group. More specifically, it tells us the number of rows that form each group you can count any column in a group, and you’ll get the same answer, so

Download 4.24 Mb.

Share with your friends: