Learning Mysql

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 inset 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) columns. Forgetting the aggregation fora 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 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

Download 4.24 Mb.

Share with your friends: