I have 2 tables, Users and Files.
Users has (id, name),
Files has (id, user_id, path).
I need to display table with Users and count of uploaded files for each user.
Add a comment
|
2 Answers
Use LEFT JOIN, GROUP BY and COUNT as follows:
SELECT Users.name, COUNT(Files.id) AS files_count
FROM Users
LEFT JOIN Files
ON Users.id = Files.user_id
GROUP BY Users.name
2 Comments
Ju-v
Thank you. Just tryied "SELECT Users.name, (SELECT count(Files.id) FROM Files WHERE Files.user_id = Users.id) as Total Uploads FROM Users" looks like it works too, second question will LEFT JOIN be better to use?
Mark Byers
Probably not much difference. It might make a difference if you want to add more columns in the future (e.g. total file size, etc) - then the LEFT JOIN would be more convenient.
select
u.id,u.name,count(f.id) as counting
from
users as u inner join files as f on u.id=f.user_id
group by
u.id,u.name
1 Comment
Mark Byers
A difference between this answer and mine is that this one won't include users with no files, because you use an INNER JOIN instead of a LEFT JOIN.