CODEWITHSUNDEEP
mysql
jmccartie
jmccartie

Reputation: 4976

Select Users by Total Submissions

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:

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;

Upvotes: 4

Views: 226

Answers (3)

Scott Stevenson
Scott Stevenson

Reputation: 90

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).

Upvotes: 0

Ed Carrel
Ed Carrel

Reputation: 4254

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.

Upvotes: 5

Ryan Guill
Ryan Guill

Reputation: 13916

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

Upvotes: 2

Related Questions