Let’s say you have the following table called votes that keeps track of how people voted and you want a query to count the number of votes for you instead of having to loop through all the rows with a counter in PHP.

Here is the MySQL query that would do just the job.

SELECT person,
SUM(IF(vote = "yes", 1,0)) AS `yes_votes`,
SUM(IF(vote = "no", 1,0)) AS `no_votes`,
COUNT(vote) AS `total`
FROM votes
GROUP BY person
ORDER BY yes_votes DESC


The following would be the result of the query:

The beauty of the query all lies in the SUM IF statements. For example, in order to count the yes votes, the IF statement will check to see if vote = “yes” as it loops through all the rows. If so, the yes_votes column alias is incremented by 1. The same procedure goes for counting the no votes. The COUNT statements keeps a tally on the total number of votes. The GROUP BY person statement allows the vote counters to calculate the numbers by person instead of the total number of yes and no votes.

Now, you do not have to loop through your MySQL results to count column values =)

• Share
• Tweet
• "&title=""">