How to get % of total using ONE query

[dschuett]

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>

[diafol]

SO are you looking for a list of:

sum specific truck / total num of trucks in DB * 100?

Have a look at this: http://forums.mysql.com/read.php?52,134684,134741#msg-134741