Having trouble figuring out how to list the number of "albums" associated with specific ar-CodePudding

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

CodePudding user response：

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

CodePudding user response：

The filtering of aggregated values should be done in the HAVING clause and you can use COUNT(*) once in the SELECT list and anywhere else use its alias:

SELECT ar.name, 
       COUNT(*) AS "Total Albums Released" 
FROM artists AS ar JOIN albums AS al
ON al.ArtistId = ar.ArtistId
GROUP BY ar.ArtistId
HAVING "Total Albums Released" >= 6
ORDER BY "Total Albums Released" DESC;