Counting votes in a MySQL table only once or twice

Question

I've got the following table:

+-----------------+  
| id| user | vote |  
+-----------------+  
| 1 | 1    | text |  
| 2 | 1    | text2|  
| 3 | 2    | text |  
| 4 | 3    | text3|  
| 5 | 2    | text |  
+-----------------+

What I want to do is to count the "votes"

SELECT COUNT(vote), vote FROM table GROUP BY vote

That works fine. Output:

+-------------------+  
| count(vote)| vote |  
+-------------------+  
| 3          | text |  
| 1          | text2|  
| 1          | text3|  
+-------------------+

But now I only want to count the first or the first and the second vote from a user. So result what I want is (if I count only the first vote):

+-------------------+  
| count(vote)| vote |  
+-------------------+  
| 2          | text |  
| 1          | text3|  
+-------------------+

I tried to work with count(distinct...) but can get it work.

Any hint in the right direction?

cann't understand your second expected output. how it comes? — Ilesh Patel, Apr 22 '14 at 09:24
Thats the ouput I want. So it counts ids: 1, 3 and 4 (all other ids have been voted by a user who already made a vote) — alex, Apr 22 '14 at 09:34
Every user voted only 2 times, so you need to include all 3 votes. Why are you excluding "text2"? — Stoleg, Apr 22 '14 at 10:14

score 1 · Accepted Answer · answered Apr 22 '14 at 10:24

1

You can do this in a single SQL statement with something like this:

SELECT vote, COUNT(vote)
FROM 
(
SELECT MAX(user), vote
FROM table1 
GROUP BY user
) d
GROUP BY vote

Note that this only gives you 1 vote not 1 or 2.

answered Apr 22 '14 at 10:24

smoore4

4,520
3
36
55

score 1 · Answer 2 · edited May 23 '17 at 12:32

The easiest way would be to use one of the "row numbering" solutions listed in this SO question. Then your original query's almost there:

SELECT
    COUNT(vote), 
    vote 
FROM tableWithRowNumberAdded 
WHERE MadeUpRowNumber IN (1,2) 
GROUP BY vote

My alternative is much longer winded and calls for working tables. These can be "real" tables in your schema, or whatever flavour of intermediate resultsets you are comfortable with.

Start by getting the first vote for each user:

SELECT user, min(id) FROM table GROUP BY user

Put this in a working table; let's call it FirstVote. Next we can get each user's second vote, if any:

SELECT user, min(id) FROM table WHERE id not in (select id from FirstVote) GROUP BY user

Let's call the result of this SecondVote. UNION FirstVote to SecondVote, join this to the original table and group by vote. There's your answer!

SELECT
    vote,
    COUNT(*)
FROM table
INNER JOIN
        (
        SELECT id FROM FirstVote
        UNION ALL
        SELECT id FROM SecondVote
        ) as BothVotes
    ON BothVotes.id = table.id
GROUP BY vote

Of course it could be structured as a single statement with multiple sub-queries but that would be horrendous to maintain, or read in this forum.

score 0 · Answer 3 · edited Apr 13 '17 at 12:42

This is a very triky question for MySQL. On other systems there windowed functions: it performs a calculation across a set of table rows that are somehow related to the current row. MySQL lacks this functionality. So one should look for a workaround. Here is the problem description and couple solutions suggested: MySQL and window functions.

I also assume that first 2 votes by the User can be determined by Id: earlier vote has smaller Id.

Based on this I would suggest this solution to your problem:

SELECT
  Vote,
  Count (*)

FROM
Table,
      (
        SELECT 
          user_id, SUBSTRING_INDEX(GROUP_CONCAT(Id ORDER BY user_id ASC), ',', 2) AS top_IDs_per_user
        FROM
          Table
        GROUP BY
          user_id
      ) s_top_IDs_per_User

WHERE
  Table.user_id = s_top_IDs_per_User.User_id and
  FIND_IN_SET(Id, s_top_IDs_per_User.top_IDs_per_user)

GROUP BY Vote
;

Counting votes in a MySQL table only once or twice

3 Answers3