Having trouble pulling info from multiple tables and getting a sum

[sanscrit]

Hi all, hopefully this has a simple solution that I'm just overlooking. What I've been able to do is single out a specific range of items in my database and have it display the items in a table along with the quantity sold. I've been struggling, without much luck, to figure out how to simply add up the quantities for those items to get a grand total at the bottom of the list. My code that displays the items and their quantities is below. Any help would be appreciated, thanks!

 

 

$list_all_query = "SELECT i.itemid, b.brand, i.item, i.quantity FROM brand as b, item as i WHERE i.brandid = b.brandid AND i.item LIKE 'CCT Time:%' ORDER BY i.item";

 

$list_all = mysql_query($list_all_query) OR DIE ('problem with query $list_all_query');

 

print("<table border=\"0\" cellpadding=\"5\" cellspacing=\"0\">");

print("<tr><td><b>Item</b></td><td><b>Quantity</b></td></tr>");

while($row = mysql_fetch_row($list_all)) {

 

print("<tr><td><a href=\"item_edit_detail.php?itemid=$row[0]\">$row[2]</a></td><td>$row[3]</td></tr>");

 

}

 

print("</table>");

 

 

[aschk]

Try

 

SELECT i.itemid
      , SUM(i.quantity) as 'sum'
FROM brand as b
JOIN item as i ON i.brandid = b.brandid 
WHERE i.item LIKE 'CCT Time:%' 
GROUP BY i.itemid
ORDER BY i.item

[rhodesa]

If the code is working already, you just need to keep adding the values to a variable:

 

<?php
$list_all_query = "SELECT i.itemid, b.brand, i.item, i.quantity FROM brand as b, item as i WHERE i.brandid = b.brandid AND i.item LIKE 'CCT Time:%' ORDER BY i.item";

$list_all = mysql_query($list_all_query) OR DIE ('problem with query $list_all_query');

$sum = 0;
print("<table border=\"0\" cellpadding=\"5\" cellspacing=\"0\">");
print("<tr><td>Item</td><td>Quantity</td></tr>");
while($row = mysql_fetch_row($list_all)) {
  print("<tr><td><a href=\"item_edit_detail.php?itemid=$row[0]\">$row[2][/url]</td><td>$row[3]</td></tr>");
  $sum += $row[3];
}
print("<tr><td>Grand Total:</td><td>{$sum}</td></tr>");
print("</table>");
?>

[sanscrit]

Thanks Rhodesa, that worked like a charm!