SQL to gather data from one table while counting records in another

Question

I have a users table and a songs table, I want to select all the users in the users table while counting how many songs they have in the songs table. I have this SQL but it doesn't work, can someone spot what i'm doing wrong?

SELECT jos_mfs_users.*, COUNT(jos_mfs_songs.id) as song_count 
FROM jos_mfs_users 
INNER JOIN jos_mfs_songs
ON jos_mfs_songs.artist=jos_mfs_users.id

Help is much appreciated. Thanks!

Answer 1

The inner join won't work, because it joins every matching row in the songs table with the users table.

SELECT jos_mfs_users.*, 
    (SELECT COUNT(jos_mfs_songs.id) 
        FROM jos_mfs_songs
        WHERE jos_mfs_songs.artist=jos_mfs_users.id) as song_count 
FROM jos_mfs_users 
WHERE (SELECT COUNT(jos_mfs_songs.id) 
        FROM jos_mfs_songs
        WHERE jos_mfs_songs.artist=jos_mfs_users.id) > 10

Answer 2

There's a GROUP BY clause missing, e.g.

SELECT jos_mfs_users.id, COUNT(jos_mfs_songs.id) as song_count 
FROM jos_mfs_users 
INNER JOIN jos_mfs_songs
ON jos_mfs_songs.artist=jos_mfs_users.id
GROUP BY jos_mfs_users.id

If you want to add more columns from jos_mfs_users in the select list you should add them in the GROUP BYclause as well.