MySQL数量和总数

Question

I have a table with a number of votes recorded in it, each vote goes against a post. What I want to be able to do is count the total occurances of each vote.

Table looks like this:

    vote
    -----
    1
    2
    3
    1
    2
    3
    4
    5
    2
    2
    2
    2
    ...and so on...

How can I count the records, e.g. In that list 1x2, 2x6, 3x2, 4x1 and 5x1 times.

Answer 1

To get what you want you can use GROUP BY and COUNT:

SELECT vote, COUNT(*) AS cnt
FROM votes
GROUP BY vote

Result:

vote  cnt
1     2  
2     6  
3     2  
4     1  
5     1  

Votes that have zero count will not be represented in this result set. If you want to have these included with a count of zero then you will need to use an OUTER JOIN with a table listing all the possible votes.

SELECT
    possible_votes.vote,
    COUNT(votes.vote) AS cnt
FROM possible_votes
LEFT JOIN votes ON possible_votes.vote = votes.vote
GROUP BY possible_votes.vote

Result:

vote  cnt
1     2  
2     6  
3     2  
4     1  
5     1  
6     0