SQL INNER JOIN of sum distinct values

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?

Answer

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