mysql counts in a group by

Question

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.

"how many times each user has voted" means "gave a vote" or "received a vote"? — Quassnoi
– Quassnoi, Commented Sep 17, 2009 at 13:39
Well, I meant given a vote. The answer was so simple. Where I tried to use a count(*) with a group by and even a count ( DISTINCT user_id ) etc etc. Quassnoi got the correct - and easy - answer. Again, thanks for this super forum. — adergaard
– adergaard, Commented Sep 17, 2009 at 13:41

Quassnoi · Accepted Answer · 2009-09-17 13:37:35Z

5

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

answered Sep 17, 2009 at 13:37

Quassnoi

427k94 gold badges627 silver badges622 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql counts in a group by

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related