Two SELECT( Count(*)) queries

[timstring]

I need a 'for()' loop or an 'if()' statement to corral a couple of 'SELECT COUNT ()' queries, one right after the other. Somewhere in my feeble brain, I think I need to boil the two into one, but I haven't found any examples of that. Here's my gibberish:

 $stmt = $mysqli->prepare("SELECT * FROM BadTechs_temp") or die('not here again?' . $mysqli->error);

$stmt->execute();
$TempResult = $stmt->get_result();
$RowCount = $TempResult->num_rows;



$FailResult = $mysqli->query("SELECT TechNum, COUNT(*) AS Fail FROM BadTechs_temp WHERE ICOMS = 'Failed' GROUP BY TechNum");
$row = $FailResult->fetch_assoc();
$Fail =$row['Fail'];



$ManualResult = $mysqli->query("SELECT TechNum, COUNT(*) AS Manual FROM BadTechs_temp WHERE ICOMS = 'Manual' GROUP BY TechNum");
$row = $ManualResult->fetch_assoc();
$Manual =$row['Manual'];



echo $LastName . ' xx ' . $Manual . ' !! ' . $Fail . '<br>';

if (($Fail + $Manual)  > 2) {


		$stmt = $mysqli->prepare("INSERT INTO BadTechs(WorkDate, Fail, Manual, TechNum, FirstName, LastName, ICOMS, WorkOrder) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
$stmt -> bind_param('ssssssss', $WorkDate, $Fail, $Manual, $TechNum, $FirstName, $LastName, $ICOMS, $WorkOrder);
$stmt ->execute();

	$BadRow[0] = $WorkDate;
	$BadRow[1] = $Manual;
	$BadRow[2] = $Fail;
	$BadRow[3] = $TechNum . ' - ' . $FirstName . ' ' . $LastName;
	$BadRow[4] = $ICOMS;
	$BadRow[5] = $WorkOrders;

fputcsv ($fh, $BadRow);


echo '<tr style = "border:2px; background-color:#FFFFFF">';
echo '<td>' . $Fail . '</td>';
echo '<td>' . $Manual . '</td>';
echo '<td>' . $TechNum . ' - ' . $FirstName . ' ' . $LastName . '</td>';
echo '<td>' . $ICOMS . '</td>';
echo '<td>' . $WorkOrder . '</td>';
echo '</tr>';
$Fail = 0;
$Manual = 0;

}

Where would I look?

ty
tim

[NogDog]

How about...

SELECT TechNum, ICOMS, COUNT(*) AS `number`
FROM BadTechs_temp 
WHERE ICOMS IN('Failed', 'Manual') 
GROUP BY TechNum, ICOMS 
ORDER BY TechNum, ICOMS

[timstring]

I put your query in the command prompt and it returned one number for each tech. I need it to return the count of both "ICOMS = 'Fail'" and "ICOMS = 'Manual'" Giving a more detailed explanation of my problem would probably be helpful.

Here's the data set I'm using:

Work Date                TechNum		Name		ICOMS
2013-05-14			9657		Robert Pasciak	Manual
2013-05-14			9657		Robert Pasciak	Failed	
2013-05-14			9657		Robert Pasciak	Manual
2013-05-14			9657		Robert Pasciak	Failed	
2013-05-14			9657		Robert Pasciak	Manual	
2013-05-14			9657		Robert Pasciak	Failed

When I run your query, I get:

2013-05-14 		9657		Robert Pasciak     Failed  5

I need the query to return:

2013-05-14			9657		Robert Pasciak   Manual 3   	Failed  3

Thanks for the speedy reply

Yes, I caught the 's' and the "

[bradgrafelman]

Switch the ordering of "TechNum" and "ICOMS" in the GROUP BY statement.