CODEWITHSUNDEEP
sqlmysqlcount
adergaard
adergaard

Reputation: 1231

mysql counts in a group by

Say I have a voting table, where users can vote on values up, down, or flat. Say a user gets a point each time the corrcet projection is made.

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

Something like:

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 get me a nice list where the "best guessing" user comes up first.

Here's my question:

How do I - in the same query - go about obtaining how many times each user has voted during the given timeperiod?

Put another way: I want a count - per row - that need to contain the number of rows found with the user_id within the above mentioned query.

Any suggestions?

Thanks.

Upvotes: 3

Views: 218

Answers (1)

Quassnoi
Quassnoi

Reputation: 425251

Just add 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

Upvotes: 5

Related Questions