Multiple Counts with 1 SQL Statement?

[tgrk35]

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 .

[kurby]

SELECT COUNT(*), Employee_Type
FROM employee
WHERE date_conditions
GROUP BY Employee_Type

[tgrk35]

SELECT COUNT(*), Employee_Type
FROM employee
WHERE date_conditions
GROUP BY Employee_Type

How would I then output that to my table rows?

[kurby]

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.

[tgrk35]

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.

[kurby]

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.

[tgrk35]

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:

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

[kurby]

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:

while ($row = mysql_fetch_assoc($result)) {
    echo $row["userid"];
    echo $row["fullname"];
    echo $row["userstatus"];
} 

Now if you modify this example.

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

[tgrk35]

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:

while ($row = mysql_fetch_assoc($result)) {
    echo $row["userid"];
    echo $row["fullname"];
    echo $row["userstatus"];
} 

Now if you modify this example.

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

[kurby]

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.

[tgrk35]

Hey, one last question. I promise.

Here's what I have:

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

[tgrk35]

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.

[tgrk35]

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?

[kurby]

Null values from your query won't be NULL they will be an empty string. You can use empty().