How to count values with MySQL queries
October 5th, 2008 | Published in mysql | 13 Comments
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 =)
Share with a friend:Customize message
![[del.icio.us]](http://images.del.icio.us/static/img/delicious.small.gif){kind=small}
November 11th, 2008 at 7:54 pm (#)
but what if you want to count an “int” of different values… for example in column “sem_id” there is
56
34
23
89
56
56
56
34
09
09
and you want to count how many of “09″ there are and how many of “56″ there are…
[Reply]
Knix reply on November 11th, 2008 9:37 pm:
You can use the GROUP BY modifier and just count the number of rows =)
[Reply]
November 12th, 2008 at 8:56 am (#)
this actually just gives me
56
34
23
89
56
56
56
34
09
09
[Reply]
Knix reply on November 12th, 2008 9:03 am:
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?
[Reply]
November 12th, 2008 at 9:06 am (#)
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];
};
[Reply]
Knix reply on November 12th, 2008 10:23 am:
Hi Evin,
The echo statement should also include your count field. Add an alias to the count and use that in the echo:
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.
[Reply]
Evin reply on November 12th, 2008 10:39 am:
nope, it just keeps counting how many values in total not grouped
$row['total']? where is this defined?
[Reply]
November 12th, 2008 at 11:24 am (#)
this did the trick!
Thanks Knix
[Reply]
Knix reply on November 12th, 2008 12:09 pm:
No problem =)
[Reply]
November 23rd, 2008 at 5:22 am (#)
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.
[Reply]
Knix reply on November 27th, 2008 9:46 pm:
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:
[Reply]
January 29th, 2009 at 5:07 am (#)
thanks a lot.. ‘group by’ did the trick for me.
[Reply]
February 8th, 2010 at 6:19 pm (#)
Thank you. Great sharing & i have search this type of search long time ago.
[Reply]