SUM function is not working

[tobitimac]

Hi

 

Can someone help me on this.. The SUM(quantityHand) is not summing up the quantityHand

 

if(isset($select)&&$select!=""){

   // Get records from database (table "name_list").
//$result=@mysql_query("select * FROM `inventory` WHERE id='$select' GROUP BY `itemNumber` ORDER BY `itemNumber`");
$result=@mysql_query("SELECT DISTINCT itemNumber, itemDesc, quantityHand, SUM(quantityHand) AS quantityHand FROM inventory  WHERE id='$select' GROUP BY `itemNumber` ORDER BY `itemNumber`");
while ($row=@mysql_fetch_assoc($result)) {

    echo"<b>";
echo "<tr><td style=\"font-size: 15px;\"><b>";
echo $row['itemNumber'];
echo "</td><td style=\"font-size: 15px;\"><b>";
echo $row['itemDesc'];
//echo "</td><td style=\"text-align: right;\"><b>";
echo "</td><td style=\"font-size: 15px; text-align: center;\"><b>";
echo $row['quantityHand'];
    echo"</b>";

// End While loop
}
// End if statement.
}

[mellis95]

For one thing, you have the same field name used twice. Once as the actual field name, and once as the name for the SUM. Change your query to something like this and see what it looks like:

$result=@mysql_query("SELECT DISTINCT itemNumber, itemDesc, quantityHand, SUM(quantityHand) AS quantityHandSUM FROM inventory  WHERE id='$select' GROUP BY `itemNumber` ORDER BY `itemNumber`");

 

 

 

 

[tobitimac]

 

Thanks Matt,

 

still the same problem. nothing changed. the quantityHand is still not summing up..

[mellis95]

Don't SELECT DISTINCT, just SELECT. I don't have a mysql server to test on right now, but I believe if you use SELECT DISTINCT it only selects each DISTINCT itemNumber. To SUM them and group you need to select duplicate item numbers.

[tobitimac]

NOTHING CHANGES.. dont why its behaving this way..

[PFMaBiSmAd]

When you changed the alias name in the query, did you change your php code to use that new name?

 

What does show up on the web page where the quantity should be? Do you get a zero or is it blank? Have you done a 'view source' in your browser, in case the output is present but is not being rendered?

 

Are you sure you have any rows that match WHERE id='$select', because the SUM() will return a null value if there are no rows. Try using var_dump($row['quantityHandSUM']) to see what you are actually getting.

 

It would help if you echoed $select to make sure it has the value you expect (just because it is set and not an empty string, doesn't mean it has the value you expect) and it would help if you showed us the rows from your table that the query should be operating on.

[tobitimac]

Thanks guy the problem is fixed.

 

All i did was to change the select to be item number since am selecting by item number and i always used echo"$select"; to see what am selecting which help to see that i need to select to item number.