Count and Group By ?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
LooseCannon
Forum Newbie
Posts: 2
Joined: Tue Feb 17, 2009 10:27 am

Count and Group By ?

Post 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
mintedjo
Forum Contributor
Posts: 153
Joined: Wed Nov 19, 2008 6:23 am

Re: Count and Group By ?

Post 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
LooseCannon
Forum Newbie
Posts: 2
Joined: Tue Feb 17, 2009 10:27 am

Re: Count and Group By ?

Post by LooseCannon »

That worked!!! Thanks!
Post Reply