SQL - Aggregated Functions - Misuse of COUNT Function

Question

I have a table that contains songs ("Tracks" is the name of my table). I can only use this table to retrieve my data.

The table contains a column with the AlbumId. So I am trying to find all the albums in this table that have at least 12 songs.

I use the below code but I get an error message (misuse of aggregate: COUNT()

SELECT 
AlbumId,
COUNT(*)
FROM Tracks
WHERE COUNT(*) > 12
GROUP BY AlbumId;

What exactly is wrong with my code and how would I be able to get the data that I want?

Thanks!

`COUNT(*)` instead of `COUNT()`, second to filter by aggregate you need to use `HAVING` instead of `WHERE` — Lukasz Szozda, Dec 22 '19 at 18:05

score 2 · Accepted Answer · answered Dec 22 '19 at 18:19

2

The correct SQL you need here is SELECT AlbumId, COUNT(TrackID) AS tracks FROM Tracks GROUP BY AlbumID HAVING COUNT(TrackID) > 12;

WHY use HAVING ??

Because you need to apply the condition of having more than 12 tracks after you group by AlbumID.

answered Dec 22 '19 at 18:19

Infamous

1 Answers1