0

Hi all!

I am working on making a dashboard for the inventory of our trucks. I'm having some problems with figuring out how to calculate the percentage of the total SUM of trucks per MAKE without running two different queries. You can see below what I have working this far, but I would rather not have to run two queries if I can avoid it. I am still learning php for the most part, so bare with me!

Thanks for you time.

//Get total SUM of trucks in inventory
	$qry = "SELECT COUNT(*) as sum FROM Assets WHERE assetStatus = 'inventory'";
	$result = sqlsrv_query($db,$qry,array(),array( "Scrollable" => 'static'));
	
	$total = 0;
	while($row = sqlsrv_fetch_array($result))
	{
		$sum = htmlspecialchars($row[0]);
		$total += $row[0];
	}


//Get Percentage of SUM for each Make
	$qry = "SELECT assetMake, COUNT(*) as sum FROM Assets WHERE assetStatus = 'inventory' GROUP BY assetMake";
			$result = sqlsrv_query($db,$qry,array(),array( "Scrollable" => 'static'));
?>
	<div style="width:500px; margin:0 auto;">
	<div style="margin-left:auto; margin-right:auto;">
	<h1 style='margin-top:30px;'>Total</h1>
	<table align="center" id="printview" class="qresults">
		<tr>
			<th>Make</th><th>Amount</th><th>Percent</th>
		</tr>
	<?
	$class1="white";
	$class2="grey";
	while($row = sqlsrv_fetch_array($result))
	{
		$make = htmlspecialchars($row[0]);
		$sum = htmlspecialchars($row[1]);
		
		$row_class = ($row_count % 2) ? $class1 : $class2;
		$percent = $sum/$total*100;
		$percentf = sprintf("%01.2f",$percent);	
		echo"<tr>
			<td style='width:200px;' class='$row_class'>$make</td>
			<td style='width:85px;' class='$row_class'>$sum</td>
			<td style='width:85px;' class='$row_class'>$percentf%</td>
			</tr>";

		$row_count++;
	}

	?>	
	<tr>
		<td align="right"><b>Total:</b></td>
		<td style="color:#3366ff;"><b><?=$total?></b></td>
	</tr>
	</table>
2
Contributors
1
Reply
2
Views
6 Years
Discussion Span
Last Post by diafol
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.