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>