Multiple Counts with 1 SQL Statement?

@tgrk35Jun 17.2010

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

to post a comment

PHP

13 Comments(s) _↴

@kurbyJun 17.2010 — #SELECT COUNT(*), Employee_Type

FROM employee

WHERE date_conditions

GROUP BY Employee_Type

@tgrk35authorJun 17.2010 — #SELECT COUNT(*), Employee_Type

FROM employee

WHERE date_conditions

GROUP BY Employee_Type[/QUOTE]

How would I then output that to my table rows?

@kurbyJun 17.2010 — #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.

@tgrk35authorJun 17.2010 — #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.[/QUOTE]

Naw, I've got that. I just didn't know how to output the individual numbers to my table rows.

@kurbyJun 17.2010 — #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.

@tgrk35authorJun 17.2010 — #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.[/QUOTE]

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>';[/CODE]

Here's what it outputs:
Array

(

[COUNT(*)] => 3

[emp_type] => USS

)
[/QUOTE]

Why does it only show the one record? There should be two different employee types.

Thanks for all your help, by the way, ?.

@kurbyJun 17.2010 — #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:

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

Now if you modify this example.

[code=php]
 $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(*)']; 

 }
 [/code]

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

@tgrk35authorJun 17.2010 — #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:

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

Now if you modify this example.

[code=php]
 $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(*)']; 

 }
 [/code]

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

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.

@kurbyJun 17.2010 — #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.

@tgrk35authorJun 17.2010 — #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'];
  }
 
 } [/CODE]

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 ?

@tgrk35authorJun 17.2010 — #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.

@tgrk35authorJun 17.2010 — #

[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;
 } 

 } [/CODE]

I did this and it works. Not sure it's the BEST approach... but maybe?

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

Also in #PHP _↴

How can i implement forum using php?Can anyone recomend a good free shopping cart setting and getting cookies...

Success!

Help @tgrk35 spread the word by sharing this article on Twitter...

Tweet This

Multiple Counts with 1 SQL Statement?

13 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

Multiple Counts with 1 SQL Statement?

13 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

13 Comments(s) _↴

Also in #PHP _↴