Mysql is calculated in a group by

Question

Say I have a voting table where users can vote up, down, or horizontally. Say the user gets a point every time the corrcet projection is executed.

At the end of each week, I want to display some statistics.

Sort of:

SELECT user_id, sum( user_points ) as sum_points FROM voting_results
WHERE voting_date > ('2009-09-18' - INTERVAL 1 WEEK)
GROUP BY user_id 
ORDER BY sum_points DESC

Fine This will give me a good list where the “best guess” user comes first.

Here is my question:

How can I - in the same query - go about how many times each user voted for a given time interval?

Put another way: I want the number per line - should contain the number of lines found with user_id as part of the above query.

Any suggestions?

Thanks.

Answer 1

COUNT(*):

SELECT  user_id,
        SUM(user_points) as sum_points,
        COUNT(*) AS num_votes
FROM    voting_results
WHERE   voting_date > ('2009-09-18' - INTERVAL 1 WEEK)
GROUP BY
        user_id 
ORDER BY
        sum_points DESC