'Output a list of CD genres (Country, New Age, Jazz, Rock etc.), in order of popularity

We have following tables:

Order_Basket:

Order_ID (PK)
CD_ID (PK)

CD:

CD_ID (PK)
Album_ID
Availability

Album:

Album_ID (PK)
Album_Name
Supplier_ID
Price
Date_Released
Date_Last_Purchased

Song:

Song_ID (PK)
Song_Name
Genre
Language
Album_ID

We assume that one album has 5 songs and this is our sql:

SELECT Genre, Count(*)
FROM Order_Basket, CD, Song, Album
WHERE Order_Basket.CD_ID=CD.CD_ID 
AND CD.Album_ID=Album.Album_ID
AND Album.Album_ID=Song.Album_ID
GROUP BY Song.Genre;

With the sql above we found total of 220 genres where we have only 44 albums sold. Every genre is repeated 5 times (44x5=220). We also try DISTINCT but the output is still the same (220 in total).

How can we do this correctly?

mysql


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions