www.webdeveloper.com
Results 1 to 14 of 14

Thread: Multiple Counts with 1 SQL Statement?

  1. #1
    Join Date
    Jan 2005
    Location
    USA
    Posts
    352

    Angry Multiple Counts with 1 SQL Statement?

    Here's the jist of what I'm trying to do:

    Use one SQL statement to pull all records from a db within a certain date range.

    From there I want to be able to count how many people answered "column1" a certain way.

    So for example I have a table with "Employee Type". Is it possible for me to do this with just one SQL statement?

    Faculty: [ # of people who answered "Faculty" in "Employee_Type" column ]
    Staff: [ # of people who answered "Staff" in "Employee_Type" column ]

    Thanks for any help I can get on this. It's been way too long since I've done any of this manual SQL/PHP stuff. I normally work in frameworks nowadays so my knowledge of the basics is... gone .

  2. #2
    Join Date
    Apr 2008
    Posts
    190
    SELECT COUNT(*), Employee_Type
    FROM employee
    WHERE date_conditions
    GROUP BY Employee_Type

  3. #3
    Join Date
    Jan 2005
    Location
    USA
    Posts
    352
    Quote Originally Posted by kurby View Post
    SELECT COUNT(*), Employee_Type
    FROM employee
    WHERE date_conditions
    GROUP BY Employee_Type
    How would I then output that to my table rows?

  4. #4
    Join Date
    Apr 2008
    Posts
    190
    Do you have an attempt to so far?

    I won't really explain how to connect to and query a database with PHP. There are plenty of tutorials out there.

  5. #5
    Join Date
    Jan 2005
    Location
    USA
    Posts
    352
    Quote Originally Posted by kurby View Post
    Do you have an attempt to so far?

    I won't really explain how to connect to and query a database with PHP. There are plenty of tutorials out there.
    Naw, I've got that. I just didn't know how to output the individual numbers to my table rows.

  6. #6
    Join Date
    Apr 2008
    Posts
    190
    It kind of depends on your method for querying.

    Your data will come in two rows(or more if you have more Employee_Types)

    12 Staff
    8 Faculty

    Loop through your dataset and the row with the second column being Staff contains the number for how many Staff Employee_Types there are. Thats the row you want to display in your table.

    If you make an attempt and post the code I can critique it for you.

  7. #7
    Join Date
    Jan 2005
    Location
    USA
    Posts
    352
    Quote Originally Posted by kurby View Post
    It kind of depends on your method for querying.

    Your data will come in two rows(or more if you have more Employee_Types)

    12 Staff
    8 Faculty

    Loop through your dataset and the row with the second column being Staff contains the number for how many Staff Employee_Types there are. Thats the row you want to display in your table.

    If you make an attempt and post the code I can critique it for you.
    Ok, here's what I got so far:

    Code:
    $emp_type = "SELECT COUNT(*),`emp_type` FROM `responses` GROUP BY `emp_type`;";
    $emp_type_results = mysql_fetch_assoc(mysql_query($emp_type));
    
    echo '<pre>';
    print_r($emp_type_results);
    echo '</pre>';
    Here's what it outputs:
    Array
    (
    [COUNT(*)] => 3
    [emp_type] => USS
    )
    Why does it only show the one record? There should be two different employee types.

    Thanks for all your help, by the way, .

  8. #8
    Join Date
    Apr 2008
    Posts
    190
    mysql_fetch_assoc only returns the data from one row.

    From the PHP site:
    Returns an associative array that corresponds to the fetched row and moves the internal data pointer ahead.

    Also from the PHP site:
    PHP Code:
    while ($row mysql_fetch_assoc($result)) {
        echo 
    $row["userid"];
        echo 
    $row["fullname"];
        echo 
    $row["userstatus"];

    Now if you modify this example.

    PHP Code:
    $staff 0;
    $faculty 0;
    while (
    $row mysql_fetch_assoc($result)) {
        if(
    $row['emp_type'] == 'Staff')
                
    $staff $row['COUNT(*)'];
        if(
    $row['emp_type'] == 'Faculty')
                
    $faculty $row['COUNT(*)'];    

    Now your staff and faculty count are stored in variables and you can place them wherever you want.

  9. #9
    Join Date
    Jan 2005
    Location
    USA
    Posts
    352
    Quote Originally Posted by kurby View Post
    mysql_fetch_assoc only returns the data from one row.

    From the PHP site:
    Returns an associative array that corresponds to the fetched row and moves the internal data pointer ahead.

    Also from the PHP site:
    PHP Code:
    while ($row mysql_fetch_assoc($result)) {
        echo 
    $row["userid"];
        echo 
    $row["fullname"];
        echo 
    $row["userstatus"];

    Now if you modify this example.

    PHP Code:
    $staff 0;
    $faculty 0;
    while (
    $row mysql_fetch_assoc($result)) {
        if(
    $row['emp_type'] == 'Staff')
                
    $staff $row['COUNT(*)'];
        if(
    $row['emp_type'] == 'Faculty')
                
    $faculty $row['COUNT(*)'];    

    Now your staff and faculty count are stored in variables and you can place them wherever you want.
    That seems to work perfectly .

    Now... one last thing. Say I have something like this: http://screencast.com/t/ODBlNTNmYWMt

    Do I have to create a new sql statement + while loop for each of the different html tables? Each table basically looks at the values of a new column.

    emp_type was one, the next is length_of_service, and so on.

    Again, thanks for all your help. Definitely appreciated.

  10. #10
    Join Date
    Apr 2008
    Posts
    190
    In a manner of speaking, you need multiple queries. COUNT is an aggregate function and requires grouping. However, there are multiple ways to attack this. You could create the multiple queries and while loops or you can one big query with multiple queries in it. Subqueries are explained here - http://dev.mysql.com/doc//refman/5.0/en/subqueries.html.

  11. #11
    Join Date
    Jan 2005
    Location
    USA
    Posts
    352
    Hey, one last question. I promise.

    Here's what I have:

    Code:
    // SQL Queries
    $queries = array('emp_type' => 'SELECT COUNT(*) AS `total`, `emp_type` FROM `responses` GROUP BY `emp_type`;',
                      'length_of_service' => 'SELECT COUNT(*) FROM `responses` GROUP BY `length_of_service`;',
                      'discrimination' => 'SELECT COUNT(*), `q9_describe` FROM `responses` GROUP BY `q9`;');
    
    // Create results array
    $results = array();
                      
    // Employee Type
    $emp_type_results = mysql_query($queries['emp_type']);
    
    while ($row = mysql_fetch_assoc($emp_type_results)){
        print_r($row);
        if($row['emp_type'] == 'USS'){
          $results['emp_type-uss'] = $row['total'];
        }
        if($row['emp_type'] == 'UPS'){
          $results['emp_type-ups'] = $row['total'];
        }
        if($row['emp_type'] == null){
        
          $results['emp_type-empty'] = $row['total'];
        }
        
    }
    My problem:

    If the 'emp_type' column is blank... I need it to count how many times it pulls blank 'emp_type's... I hope that makes sense.

    I've attempted it up there... but doesn't seem to work at all. Doesn't display anything. I could set $results['emp_type-empty'] to 0 ahead of the while(), but I'd rather not have to do that.

    Any way around this?

    Thanks so much

  12. #12
    Join Date
    Jan 2005
    Location
    USA
    Posts
    352
    I forgot to mention. I don't currently have any db records with a blank 'emp_type'. I think if I did, my code would work just fine.

  13. #13
    Join Date
    Jan 2005
    Location
    USA
    Posts
    352
    Code:
    while ($row = mysql_fetch_assoc($emp_type_results)){
        print_r($row);
        if($row['emp_type'] == 'USS'){
          $results['emp_type-uss'] = $row['total'];
        }
        if($row['emp_type'] == 'UPS'){
          $results['emp_type-ups'] = $row['total'];
        }
        if($row['emp_type'] == null){    
          $results['emp_type-empty'] = $row['total'];
        }
        if($row['emp_type'] != null){
          $results['emp_type-empty'] = 0;
        }    
    }
    I did this and it works. Not sure it's the BEST approach... but maybe?

  14. #14
    Join Date
    Apr 2008
    Posts
    190
    Null values from your query won't be NULL they will be an empty string. You can use empty().

Thread Information

Users Browsing this Thread

There are currently 2 users browsing this thread. (0 members and 2 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles