www.webdeveloper.com
Results 1 to 14 of 14

Thread: Multiple Counts with 1 SQL Statement?

Hybrid View

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

  8. #8
    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.

  9. #9
    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?

  10. #10
    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 1 users browsing this thread. (0 members and 1 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