Random Snippets

  • Home
  • Sequence analysis blog
  • About
  • Categories
    • javascript
    • mysql
    • php
  • Subscribe via RSS

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


[Slashdot] [Digg] [Reddit] [del.icio.us] [Facebook] [Technorati] [Google] [StumbleUpon]

Responses

Feed Trackback Address
  1. Evin says:

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

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

    [Reply]

  2. Evin says:

    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]

  3. Evin says:

    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:

    $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.

    [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]

  4. Evin says:

    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]

  5. Quentin Nell says:

    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:

    SELECT SUM(fee) as total_sum
    FROM members;

    [Reply]

  6. mike says:

    January 29th, 2009 at 5:07 am (#)

    thanks a lot.. ‘group by’ did the trick for me.

    [Reply]

  7. Subhee says:

    February 8th, 2010 at 6:19 pm (#)

    Thank you. Great sharing & i have search this type of search long time ago.

    [Reply]

Comments or feedback...

If you have any demos that you would like to request, please do so.

Click to cancel reply

Recent Posts

  • Sorting 2D associative arrays in PHP
  • Dynamic or on-the-fly percentage calculations with JavaScript
  • The dangers of embedding the notorious “void(0)” JavaScript code in the href attribute of the “a” tag
  • How to randomly order or select rows in a MySQL query
  • How to convert MySQL timestamp to PHP date type

Recent Comments

  • drei on Simulate a button click via JavaScript
  • Subhee on How to count values with MySQL queries
  • Russell Day on How to hide, show, or toggle your div
  • phi on How to hide, show, or toggle your div
  • bornholy on How to hide, show, or toggle your div

Archives

Categories

  • javascript
  • mysql
  • php

Tag Cloud

add addition and subtraction calculator checkboxes checkEmail content demo demo content div id document getelementbyid dynamic Dynamically dynamic content emailRegEx find form getElementById html javascript input buttons input object input text javascript javascript code javascript demo javascript email javascript function javascript functions loop through menu multiplication mysql mysql query onClick onclick event query regex remove removeChild removeElement replace simulate styling valid email address verification verifyEmail

©2010 Random Snippets