Creating table statistics

HTTP-404

Hello,

I have table with messages, and i have users table, the are linked via PK
UserID -> UserID, what i need is to select Messages for current month and
calculate top 5 posters list for current month.

I have create following query but it's not complete:

SELECT Handle, Screen_Name from MBoard where Message_Date>='2003-11-00' ORDER BY Handle;

this gets me all messages posted this month ordered by userid

Thank You.

tekky

Something like....

 
SELECT Handle, Screen_Name, count(*) as posts from MBoard where Message_Date>='2003-11-00' GROUP BY Handle ORDER BY posts DESC LIMIT 5;

I believe that would od the trick...

basically would count each row returned which has the same handle... return the total as posts, order by posts descending... so 10 9 8 7.... limiting the rows to the 5 highest in order as said previously....

(may need a little tweaking... my GROUP BY's seldom work the first time....)

[edit -- just tested... on my phpBB database... worked fine 🙂 --]

sebconn

Brilliant, I was looking for a very similar query for my Winamp Stats page and that works great, thanks!

tekky

Originally posted by sebconn
Brilliant, I was looking for a very similar query for my Winamp Stats page and that works great, thanks!

Wooooooo! Congratulations! You are a shining star in the paradign of "searching never pays off" Glad an old archive was able to help you out! Even more suprized that my group by worked on the first attempt! 😃

You are officially an un-newbie on these forums! By the way, thanks for actually posting that we were able to help you out unlike the original author here... :eek:

sebconn

here's the finished product if you're interested