Using COUNT in SQL Server doesn't show null values

Question

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.

Be aware that string literals as column aliases is on the deprecation list (e.g. COUNT(*) AS 'alias'), and is already not supported in Azure. Use <expression> AS [Alias] or [Alias] = <expression> to avoid compatibility problems with future versions of SQL Server. A good article on this is Bad Habits to Kick : Using AS instead of = for column aliases — GarethD
– GarethD, Commented Nov 19, 2014 at 10:10

juergen d · Accepted Answer · 2014-11-19 10:47:44Z

2

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

edited Nov 19, 2014 at 10:47

answered Nov 19, 2014 at 9:53

juergen d

205k40 gold badges303 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Christopher Over a year ago

Thanks for the fast answer, but this still does not show songs that aren't on any playlist.

GarethD Over a year ago

Should to be a left join to Playlist too, although this join is not actually required at all, could just join to playlist song and use COUNT(DISTINCT playlistsong.PlayListID)

Christopher Over a year ago

Yes, I just tried using left join on both, and it worked. Thanks.

GarethD Over a year ago

@Christopher, Since this answer helped you should consider choosing it as the accepted answer, this shows others that it helped, and will also remove the question from the unanswered pile.

Christopher Over a year ago

@GarethD Think I did it now, thanks. This is the first time I've created a thread :)

Collectives™ on Stack Overflow

Using COUNT in SQL Server doesn't show null values

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related