How to count values with MySQL queries

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.

person vote
obama yes
mccain no
obama yes
obama no
mccain yes
obama yes
obama yes
obama no
mccain no

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:

person yes_votes no_votes total
obama 4 2 6
mccain 1 2 3

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 =)

Published by

Allen Liu

Building websites is a passion of mine. During the course of my work, I have created some snippets of code that I hope will prove useful to other developers.

22 thoughts on “How to count values with MySQL queries”

  1. Thanks for great post. Just would like to ask if its possible to to find the difference between yes votes and no votes for each candidate for example obama has 4 yes votes and 2 no votes. so i would like to get 4-2 is this possible in the query itself? Thanks.

  2. Thank you so much for this. This is EXACTLY what I was looking for. FYI if you want to do something where you sort by the total tally of upvotes and downvotes (with positive numbers at the top, 0s in the middle, and negative numbers at the bottom), try this:

    SELECT (SUM(IF(vote = 1, 1,0)) – SUM(IF(vote = 0, 1,0))) AS consensus
    FROM votes
    GROUP BY person ORDER BY consensus DESC

  3. Hi, i have a membership site iam working on… tables are as follows ID,name,email and monthly fee. now i don’t want to count the total records, i would like to count and add the totals of each monthly fee record to see what is the total income of all the members “Records” are.

    1. Hi Quentin,

      You can just total up the monthly fee field using the MySQL aggregate function SUM. Here is an example of the usage assuming the monthly fee field is just called fee and your table is called members:

      SELECT SUM(fee) as total_sum
      FROM members;
  4. this is what i have, had it in a while loop now its in the “if” statement and it just counts all values in the column together without distinguishing or grouping values that are the same

    $db = mysqli_connect( “localhost” , “tester”, “123456” , “tester” )
    or die (“not able to connect.”);

    $query2 = (“SELECT sem_id, count(*) FROM `attendees` GROUP BY sem_id”);
    $result2 = mysqli_query($db , $query2);

    if ($row = mysqli_fetch_assoc($result2 ))


    echo $row[sem_id];


    1. Hi Evin,

      The echo statement should also include your count field. Add an alias to the count and use that in the echo:

      $query2 = (”SELECT sem_id, count(*) as total FROM `attendees` GROUP BY sem_id”);
      echo "sem_id: " . $row['sem_id'] . " total: " . $row['total'] . "<br />";

      I would run the raw query directly in the MySQL command line first to see if it works. This way, you do not need to worry about PHP just yet. Your query looks good and should work.

    1. Hi Evin,

      It almost seems like the GROUP BY modifier was not in the query.

      Can you please give me the schema of your table so I can duplicate it?

  5. but what if you want to count an “int” of different values… for example in column “sem_id” there is


    and you want to count how many of “09” there are and how many of “56” there are…

    1. You can use the GROUP BY modifier and just count the number of rows =)

      1. SELECT sem_id, count(*) FROM `name_of_your_table` GROUP BY sem_id

Leave a Reply

Your email address will not be published. Required fields are marked *