COUNT clause example

[Admin]

Here is the problem:

Question 9

All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram to familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

Run Query: Find the albums with 12 or more tracks.

Here is my tentative solution:

SELECT 
Name, AlbumId, TrackId, COUNT (AlbumId > 11) AS countalbumid
FROM 
Tracks
GROUP BY
AlbumId, TrackId

Source: https://www.coursera.org/learn/sql-for-data-science%5B/caption%5D

Also, I could see Album Id 1 has 10 tracks as per first page. How do I count and get output of 10 against Album Id 1?

It will help to have clue on how to summarize AlbumId 1 against Tracks. For instance, AlbumId 1 has 10 Tracks. How to count 10 under AlbumId 1. That way counting will I understand yield result. AlbumId 1 occurs 10 times in column 1. So, it is associated with 10 tracks. Same for AlbumId 2 and so on.

Also, it is not clear to me what COUNT(AlbumId) doing against each row with an output of 1.

---

Reply

---

Your grouping is wrong as there are non-aggregates in SELECT missing in GROUP BY. Better to refer here rather than taking short cuts.

However, the main issue is — COUNT (AlbumId > 11)

The comparison results in TRUE/FALSE — and how do you count values that are TRUE or FALSE?

(some databases, like MySQL, convert TRUE and FALSE to 1 and 0, so you’d be counting integers, but that’s not the problem here)

Albumid is a column value, so do you really want to count the albums that have an id that’s greater than 11?

---

The remedy is HAVING clause

SELECT AlbumId, COUNT(AlbumId) AS countalbum
FROM Tracks
GROUP BY AlbumId
HAVING countalbum > 11

---

Also, it is not clear to me what COUNT(AlbumId) doing against each row with an output of 1.

you queried for counts for every track on every album

GROUP BY AlbumId, TrackId

so for each track on each album, you get a count

and since each track exists on its album only once, that count is 1

---

“Also, I could see Album Id 1 has 10 tracks as per first page. How do I count and get output of 10 against Album Id 1?”

easy, just group by album

how many tracks in an album? you count them for each album

so you need GROUP BY AlbumId

SELECT AlbumId, COUNT(AlbumId) AS countalbum FROM Tracks GROUP BY AlbumId HAVING countalbum > 11

[Author]

Posts