so I’m doing an SQL exercise that requires me to list the names of artists that made more than 6 albums.
I’ve successfully managed to list the names of the artists with 6+ albums and the first album that comes up in the table for each artist, however I can’t figure out how to list the number of albums.
I’m working with two tables. "artists" and "albums".
- "artists" contains "artistid" and "name"
- "albums" contains "artistid and "albumid"
Here is my sql code, and the current output vs expected output can be found in the link below. If someone can point me in the right direction, I’d really appreciate it. Note that I’m only supposed to use COUNT once.
SELECT artists.name, albums.albumid
from albums join artists on albums.ArtistId = artists.ArtistId
GROUP BY artists.artistid
HAVING COUNT(albums.albumid) > 6
https://i.stack.imgur.com/EYmyZ.png
EDIT: I have figured out that this gives the correct output, however I am only supposed to be using the COUNT function once, are there any alternatives?
SELECT artists.name, COUNT (albums.albumid) AS 'Total Albums Released'
from albums join artists on albums.ArtistId = artists.ArtistId
GROUP BY artists.artistid
HAVING COUNT(albums.albumid) >= 6
ORDER BY COUNT('Total Albums Released') DESC
>Solution :
The HAVING clause only acts as a filter, it doesen’t return a column in itself. Thats still goes in the select statement.
Edit: The Where clause allows you to apply a filter to the entire table rather than just the selected group, letting you use your aggregate functions
SELECT artists.name, COUNT (albums.albumid) AS 'Total Albums Released'
from albums join artists on albums.ArtistId = artists.ArtistId
WHERE 'Total Albums Released' >= 6
GROUP BY artists.artistid
ORDER BY COUNT('Total Albums Released') DESC