I have 3 tables called musics, singers and playlistInfos. I want to get Top 10 listened England musics.What kind of sql query help me?
musics musicID | singerID 1 1 2 1 3 2 singers singerID | name | country 1 Adele England 2 Mozart Austria 3 DuaLipa England playlistInfo id | singerID | musicID | listened 1 1 1 25 2 3 3 15 3 2 2 20
SELECT * FROM musics m INNER JOIN playlistInfo p ON p.musicID = m.id INNER JOIN singer a ON a.id = m.singerID GROUP BY p.musicID ORDER BY p.listened
I try this one but I did not get result what I was expecting.How can ı fix it?
Well first of all your sample data was wrong. So first lets create the right structure.
And please keep in mind, in future if you share sql scripts to create your sample data, you will get more answers.
-- declare tables DECLARE @singers TABLE (singerID INT, name NVARCHAR(255), country NVARCHAR(255)); DECLARE @musics TABLE (musicID INT, singerID INT, songName NVARCHAR(255)); DECLARE @playlistInfo TABLE (singerID INT, musicID INT, listened INT); -- Load sample data INSERT INTO @singers VALUES (1, 'Adele', 'England'), (2, 'Mozart', 'Austria'), (3, 'DuaLipa', 'England') INSERT INTO @musics VALUES (1, 1, 'Rolling in the Deep'), (2, 2, 'Symphony No 40'), (3, 3, 'One Kiss') INSERT INTO @playlistInfo VALUES (1, 1, 25), (2, 2, 15), (3, 3, 20)
And then query our tables for the top 10 singers from England.
SELECT TOP 10 s.name as Singer, ISNULL(SUM(pl.listened), 0) as TotalListened FROM @singers s LEFT JOIN @musics m ON m.singerID = s.singerID LEFT JOIN @playlistInfo pl ON pl.musicID = m.musicID AND pl.singerID = m.singerID -- I did left join to show anyone with 0 listen too, you can convert it to `JOIN` WHERE s.country = 'England' GROUP BY s.name ORDER BY SUM(pl.listened) DESC
Some little extra (if you want to get most listened song)
-- get the most listened song within country SELECT TOP 10 s.name as Singer, m.songName, ISNULL(SUM(pl.listened), 0) as TotalListened FROM @singers s LEFT JOIN @musics m ON m.singerID = s.singerID LEFT JOIN @playlistInfo pl ON pl.musicID = m.musicID AND pl.singerID = m.singerID WHERE s.country = 'England' GROUP BY s.name, m.songName ORDER BY SUM(pl.listened) DESC