Page 1 of 1

Count and Group By ?

Posted: Tue Feb 17, 2009 10:29 am
by LooseCannon
I'm working on a blogging site and one of the customer requirements is to display the "Top 5 Bloggers". Meaning a list of the 5 most active users.

Following are the tables and as you would guess the BLOG table stores blog data and BLOG_COMMENT stores comments/replies to a blog.

Question: Is there a pure SQL approach to get a collective count of users from both tables?

I can achieve this with 2 queries then process the results with application code but would much rather do it with one SQL statement (if possible)


blog
--------
blog_id
title
content
user_id



blog_comment
-------------
blog_id
comment_id
content
user_id

Re: Count and Group By ?

Posted: Tue Feb 17, 2009 10:37 am
by mintedjo
maybe...

Code: Select all

SELECT COUNT(*),field_id FROM (SELECT user_id FROM posts UNION ALL SELECT user_id FROM replies) as A GROUP BY field_id
But dont hold me to that, its just a suggestion :-) and if it DOES work then there are probably better ways of doing it anyway :-P

Re: Count and Group By ?

Posted: Tue Feb 17, 2009 11:32 am
by LooseCannon
That worked!!! Thanks!