Select Users by Total Submissions

Question

I know this is an easy one, but it's driving me nuts...

I have a users table, comments table, and pictures table.

I want a list of the top 10 users based on submissions (total of their comments and their submitted photos).

That's it.

Shame me.

---

UPDATE: based on Ed's answer.

here's my setup:

• users table (user_id, username)
• images table (img_id, submittedby_id = users.user_id)
• comments table (id, submittedby_id = users.user_id)

and the final query:

    select submittedby_id, sum(total)
from 
    (select submittedby_id, count(img_id) as total from    
          images group by submittedby_id 
     union 
     select submittedby_id, count(id) as total from 
          comments group by submittedby_id
    ) as x
 group by submittedby_id 
 order by sum(total) desc limit 10;

Answer 1

psuedocode of course but you want something like this:

select 
  u.userid
, count(commentID) + count(photoID) as totalsubmissions 
from users u
left outer 
    join comments c
    on u.userid = c.userid 
left outer 
    join pictures p 
    on u.userid = p.userid 
group by 
    u.userid 
order by 2 desc 
fetch first 10 rows only

Answer 2

Maybe something kind of like this:

select username, sum(submissions) 
from 
    (select username, count(picture_id) from    
          pictures group by username 
     union 
     select username, count(comment_id) from 
          comments group by username
    )
 group by username 
 order by sum(submissions) desc limit 10;

To overview conceptually:

1. Count the submissions of the user in each table
2. Union those, so each user will have between 0 and 2 counts from the subquery.
3. Group one more time, summing the two counts, and then order so that the highest amount is on top.

Hope this helps.

Answer 3

Tweaking Ed's answer:

select submittedby_id, sum(submissions) 
from 
    (select submittedby_id, count(img_id) as submissions from    
          images group by submittedby_id 
     union all
     select submittedby_id, count(id) as submissions from 
          comments group by submittedby_id
    ) as x
 group by submittedby_id 
 order by sum(submissions) desc limit 10

I believe you want to do a union all here, just union could omit records that look identical (same id and submission count).