Member Avatar for jrotunda85

I'm trying to figure out the best way to accomplish the computation of weighted averages for my site. Here's the PHP that pulls down the results --

$SQL = "SELECT a.*, b.CName, c.Description FROM humidor a
LEFT JOIN cigar b on a.CigarID = b.CigarID
LEFT JOIN cd_manufacturer c on b.Manufacturer = c.Code
WHERE UserID = $UserID AND DisplayCD = 1 AND Quantity > 0
GROUP BY CigarID
ORDER BY c.Description, b.CName ASC";
$Result = mysql_query($SQL); }

Then it goes into a WHILE loop --

while ($row = mysql_fetch_row($Result)) {
			$convertpurchasedate = strtotime($row[3]);
			echo("<tr style=\"vertical-align:middle\"><td><a href=\"../cigar?id=$row[2]\">$row[12] $row[11]</a></td><td>".date('F d, Y', $convertpurchasedate)."</td><td>$row[4]</td><td>$ $row[5]</td>
<td><input id=\"e$row[2]\" type=\"submit\" value=\"Edit\" name=\"edit\" style=\"width:47px;font-size: x-small\" onclick=\"return UpdateRecord(this.parentNode.parentNode.childNodes[2].innerHTML,this.id.substr(1));\" /><input id=\"d$row[2]\" type=\"submit\" value=\"Delete\" name=\"delete\" style=\"width:47px;font-size: x-small\" onclick=\"return DeleteRecord(this.id.substr(1));\" /><br /><input id=\"u$row[2]\" type=\"submit\" value=\"Smoke One\" name=\"use\" style=\"width:95px;font-size: x-small\" onclick=\"return UseCigar(this.parentNode.parentNode.childNodes[2].innerHTML,this.id.substr(1),'$row[2]','$row[9] $row[8]');\" /></td></tr>");}

What I'm trying to do is two-fold --

(1) I want to calculate a weighted average based on the number of cigars in inventory currently. I've figured out how to calculate it (I'm going to use something like: $TotalPrice / COUNT($Quantity) ). The issue with this is I'm not sure how to do this within the loop.
(2) Similar to #1, I want to group the cigars by CigarID such that only the most recent date added displays (I'm sure this is simple using the (MAX) function).

I figure this is probably something straightforward, it's just been one of those days and I am having a mental block.... :-/

  • 1 Contributor
  • 1 Reply
  • 124 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by jrotunda85

Dani AI

Generated

Nice that you got it working, . A couple of concrete notes that clarify the two common, robust ways to do what you described (weighted average by quantity, and show only the most‑recent entry per CigarID).

Let the database do the math (recommended): compute the weighted average with SUM(price * quantity)/SUM(quantity) and get the most recent date with MAX(). Then join that aggregate back to the humidor rows to pull the columns you want to display. Example pattern (adjust column names to match your schema):

SELECT h.*, c.CName, m.Description, agg.weighted_avg
FROM humidor h
JOIN cigar c ON h.CigarID = c.CigarID
JOIN cd_manufacturer m ON c.Manufacturer = m.Code
JOIN (
  SELECT CigarID,
         SUM(Price * Quantity) / SUM(Quantity) AS weighted_avg,
         MAX(PurchaseDate) AS latest_date
  FROM humidor
  WHERE UserID = ? AND DisplayCD = 1 AND Quantity > 0
  GROUP BY CigarID
) agg ON agg.CigarID = h.CigarID AND agg.latest_date = h.PurchaseDate
WHERE h.UserID = ? AND h.DisplayCD = 1 AND h.Quantity > 0
ORDER BY m.Description, c.CName;

If you prefer to do it in PHP (or need custom logic), accumulate totals while iterating and keep the latest row per CigarID in an associative array:

$totalQty = 0;
$totalPriceQty = 0;
$latestByCigar = [];

while ($r = mysqli_fetch_assoc($res)) {
  $q = (int)$r['Quantity'];
  $p = (float)$r['Price'];
  $totalQty += $q;
  $totalPriceQty += $p * $q;

  $cid = $r['CigarID'];
  $ts = strtotime($r['PurchaseDate']);
  if (!isset($latestByCigar[$cid]) || $ts > $latestByCigar[$cid]['ts']) {
    $latestByCigar[$cid] = ['row' => $r, 'ts' => $ts];
  }
}

$weighted = $totalQty ? $totalPriceQty / $totalQty : 0;

Quick tips: cast values to numeric before math, check for division by zero, avoid SELECT * and nonaggregated GROUP BY (MySQL’s loose behavior can return indeterminate rows), prefer prepared statements (PDO/mysqli), and add an index on (UserID, CigarID, PurchaseDate) if the table grows. If you use MySQL 8+, window functions (ROW_NUMBER()) can simplify selecting the latest row per group.

Member Avatar for jrotunda85

Actually figured it out, seems I was having a bad day.... :yawn:

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.