Reputation: 4976
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
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
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:
Hope this helps.
Upvotes: 5
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