Trouble with an aggregate SQL statement

Question

This link will show you my schema and the contents of all tables involved.

My goal is to, using a single Select statement, display the name of every artist and sort by the number of "Rock" songs they have even for the artists that do not have any, in order of fewest to most. Here is what I tried, and it obviously did not work.

SELECT 
    Musical_genre.musical_genre_id, 
    COUNT(Musical_genre.musical_genre_id) AS nr_rocksongs 
FROM 
    Musical_genre
JOIN 
    Album ON Album.musical_genre_id = Musical_genre.musical_genre_id
JOIN 
    Recording_artist ON Album.recording_artist_id = Recording_artist.recording_artist_id
GROUP BY 
    Album.recording_artist_id, Recording_artist.artist_name, Musical_genre.musical_genre_id
ORDER BY 
    nr_rocksongs ASC

Any ideas what I missed to get the results I am looking for? All help is greatly appreciated.

Sorry everyone, I totally botched one particular thing here. The COUNT should be counting song_id, not musical_genre_id. Any tips on how to make this switch as painless as possible? — user121168
– user121168, Commented Apr 5, 2017 at 15:46

ScaisEdge · Accepted Answer · 2017-04-04 19:34:45Z

1

Remove the Musical_genre.musical_genre_id from group by and add the artist_name in select

SELECT Musical_genre.musical_genre_id, Recording_artist.artist_name, 
      COUNT(Musical_genre.musical_genre_id) AS nr_rocksongs 
FROM Musical_genre
JOIN Album ON Album.musical_genre_id = Musical_genre.musical_genre_id
JOIN Recording_artist ON Album.recording_artist_id = Recording_artist.recording_artist_id
GROUP BY  Musical_genre.musical_genre_id, Recording_artist.artist_name
ORDER BY nr_rocksongs ASC

answered Apr 4, 2017 at 19:34

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user121168 Over a year ago

Thank you for your reply. That gets me past the error I was getting, so thank you. Now I just need it to only list the number of "Rock" songs per artist. I tried using "WHERE musical_genre_id = 201" but it kept erroring out. Can the COUNT option be more granular than just the column or is there some other way I can accomplish this you can think of?

Steve Lovell Over a year ago

Hasn't this answer lost you the artists without a rock album? I thought you want to keep them.

user121168 Over a year ago

Yeah, it ended up counting ALL songs, instead of just rock songs as well.

Steve Lovell · Accepted Answer · 2017-04-05 15:57:51Z

0

You need to start with the artist and left join the album and genre.

SELECT
    ra.artist_name,
    mg.musical_genre_id,
    mg.musical_genre,
    COUNT(mg.musical_genre_id) AS nr_rocksongs
FROM
    Recording_artist ra
    LEFT JOIN Album a ON
        a.recording_artist_id = ra.recording_artist_id
    LEFT JOIN Musical_genre mg ON
        a.musical_genre_id = mg.musical_genre_id
        AND mg.musical_genre = 'Rock' 
        --use musical_genre_id = 201 if you like, but then you could remove the last left join entirely.
    LEFT JOIN Song s ON
        a.album_id = s.album_id
GROUP BY
    mg.musical_genre_id,
    mg.musical_genre,
    ra.artist_name
ORDER BY
    nr_rocksongs ASC

edited Apr 5, 2017 at 15:57

answered Apr 4, 2017 at 22:05

Steve Lovell

2,5742 gold badges15 silver badges18 bronze badges

4 Comments

user121168 Over a year ago

This appears to do what I need it to. Thanks!

user121168 Over a year ago

Oops! I just realized this isn't counting songs at all! That's my fault. How can I get it to COUNT song_id instead without adding too many new lines of code?

Steve Lovell Over a year ago

No worries, I did wonder about that actually. We were counting albums before. I've added an extra join to the Songs table. The rest of the code should be fine as it is since it's the album not the song which has the genre. The counts will increase automatically due to joining the extra table.

user121168 Over a year ago

You rock Steve!

Collectives™ on Stack Overflow

Trouble with an aggregate SQL statement

2 Answers 2

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related