Counting rows where certain conditions are met

[ahsroth]

I am very much a beginner, so I apologise if this is a stupid question.

I'm trying to make a Summary page. The idea is that it will display a table in this sort of format -

 

---------------------------------

TYPE    RELEASED  IN CARE TOTAL

------------------------------------------

Birds        5              3              8

Mammals  7              5            12

------------------------------------------

Total        12            8              20

 

So it will pull all records from the table where type = birds. The total number of bird records would be the total. That bit is working fine.

The bit I'm having trouble with is getting it to do the released and in care columns. Whether an animal is released/in care is specified in a field called fate.

So I need it to look at the fate, count them, and put them into the right spot.

 

I'm also not sure on how to do the totals row at the bottom.

 

Here is what I have so far.

	$query = "SELECT type,COUNT(species) FROM animalrecord GROUP BY type";  
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){
$speciescount = $row['COUNT(species)'];
$type = $row['type'];

 

I'm not sure where to go from here and would really appreciate some help.

[ahsroth]

Anyone? Is it even possible?

[Muddy_Funster]

let's see your actual table data and we'll take it from there.

[DavidAM]

There are a couple of ways to do this. If there are a small number of "fates" and they are not likely to change, then we can do it in the query. As the number of "fates" increases the query becomes cumbersome to maintain. In that case, I would group by the type and fate, and then handle the "crosstab" in PHP.

 

 

We can start with your base query

SELECT type, COUNT(species) FROM animalrecord GROUP BY type

 

and add counters for the known "fate"

 

SELECT type, COUNT(species) AS Total,
  SUM(IF(fate="released", 1, 0)) AS Released,
  SUM(IF(fate="incare", 1, 0)) AS InCare
FROM animalrecord 
GROUP BY type

 

In this query, we are adding (SUM) one (1) for each row that meats our "fate". As you can see every "fate" you add, requires an additional line in the query. So, when there are many "fates", or we need to be able to add to the list often, it would be easier to group on fate and have PHP build the table:

 

SELECT type, fate, COUNT(species) AS Total
FROM animalrecord 
GROUP BY type, fate

 

As to the bottom totals for the table, I think you will just have to accumulate the values (in PHP) as you output them, and then print the totals from the accumulators.

 

[mikosiko]

 

As to the bottom totals for the table, I think you will just have to accumulate the values (in PHP) as you output them, and then print the totals from the accumulators.

 

 

many times GROUP BY ... WITH ROLLUP could be used too...

http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html

[DavidAM]

mikosiko is correct. Somehow, I always forget about that. It could be used here, it will be returned as the last row in the result set, with the "type" column being NULL.

 

SELECT type, COUNT(species) AS Total,
  SUM(IF(fate="released", 1, 0)) AS Released,
  SUM(IF(fate="incare", 1, 0)) AS InCare
FROM animalrecord 
GROUP BY type WITH ROLLUP

 

type	Total	Released	InCare
Birds	5	3		8
Mammals	7	5		12
NULL	12	8		20

 

Since PHP sees this as just another row in the resultset, you have to "watch" for it in your loop if you want the output to be styled in some way that makes it obvious that it is the "Total" row.

 

Also, you have to be careful if it is possible that (in this case) "type" will ever contain a NULL (in the table).