CODEWITHSUNDEEP
sql-server
Christopher
Christopher

Reputation: 83

Using COUNT in SQL Server doesn't show null values

Select all songs together with the number of playlists they are member of.

Select 
    Title, Count(*) as 'number of playlists they are member of' 
from 
    Song 
inner join 
    PlaylistSong on PlaylistSong.songID = Song.ID
inner join 
    Playlist on Playlist.ID = playlistsong.PlayListID
group by 
    Song.title

This solution almost works, but it doesn't show songs that are not assigned to any playlist. Are there any way to include those songs?

Please let me know if you need more information.

Upvotes: 0

Views: 67

Answers (1)

juergen d
juergen d

Reputation: 204784

Use a left join instead

Select Song.Title, 
       Count(distinct Playlist.ID) as 'number of playlists they are member of' 
from Song 
left join PlaylistSong on PlaylistSong.songID = Song.ID 
left join Playlist on Playlist.ID = playlistsong.PlayListID 
group by Song.title

Upvotes: 2

Related Questions