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

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.开发者_Python百科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!

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

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.

Changes:

• Don't do SELECT *...specify your fields. I included ID and NAME, you can add more as needed but put them in the GROUP BY as well

• Changed to a LEFT JOIN - INNER JOIN won't list any users that have no songs

• Added the GROUP BY so it gives a valid count and is valid syntax

  SELECT u.id, u.name COUNT(s.id) as song_count 
  FROM jos_mfs_users AS u
  LEFT JOIN jos_mfs_songs AS S
      ON s.artist = u.id
  GROUP BY U.id, u.name
  

Try

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

This seems like a many to many relationship. By that I mean it looks like there can be several records in the users table for each user, one of each song they have. I would have three tables.
Users, which has one record for each user
Songs, which has one record for each song
USER_SONGS, which has one record for each user/song combination
Now, you can do a count of the songs each user has by doing a query on the intermediate table. You can also find out how many users have a particular song.

This will tell you how many songs each user has

select id, count(*) from USER_SONGS
GROUP BY id;

This will tell you how many users each song has

select artist, count(*) from USER_SONGS
GROUP BY artist;

I'm sure you will need to tweak this for your needs, but it may give you the type of results you are looking for.

You can also join either of these queries to the other two tables to find the user name, and/or artist name.

HTH
Harv Sather

ps I am not sure if you are looking for song counts or artist counts.

You need a GROUP BY clause to use aggregate functions (like COUNT(), for example)

So, assuming that jos_mfs_users.id is a primary key, something like this will work:

SELECT jos_mfs_users.*, COUNT( jos_mfs_users.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

Notice that

• since you are grouping by user id, you will get one result per distinct user id in the results
• the thing you need to COUNT() is the number of rows that are being grouped (in this case the number of results per user)