php mysql if sum not working

Question

trans_id  trans_product  trans_user  trans_date  trans_date2  trans_description  trans_inventory
     13         6              1     1278604284   1278547200                     -1000
      9         5              1     1278601462   1278547200    New Arrival        200
     11         7              1     1278601743   1278547200                        50
     12         6              1     1278601756   1278547200                      5000

$sql = mysql_query("SELECT *, SUM(IF(trans_inventory>0,trans_inventory,0)) as pos "
                  +"FROM site_trans GROUP BY trans_product") or die(mysql_error());
while($row = mysql_fetch_array($sql)) {
    $amt_p = $row['pos'];
    $sql2 = mysql_query("SELECT *, SUM(IF(trans_inventory<0,trans_inventory,0)) as neg "
                      + "FROM site_trans GROUP BY trans_product") or die(mysql_error());
    while($row2 = mysql_fetch_array($sql2)) {
        $amt_n = $row2['neg'];
    }

    echo $amt_p; //working
    echo $amt_n; // not working
}

First sql query is working ($amt_p) however second one is for negative isn't working($amt_n) any idea what im doing wrong?

Answer 1

Not sure why it's not working, but it looks to me that you just want all the positive and negative trans_inventory rows. How about you add "where trans_inventory>0" to your first query and get rid of that conditional IF hoohah?

SELECT *, SUM(trans_inventory) as pos 
FROM site_trans
WHERE trans_inventory>0
GROUP BY trans_product

And what exactly do you mean by "not working"? What values are you getting, and what were you expecting?

Answer 2

Add $amt_n = 0 to initialise it
change $amt_n = $row2['neg']; to $amt_n += $row2['neg'];

the problem is you are zeroing the value when iterating over it

You need to do this for both pos and neg

DC

Answer 3

You're overwriting $amt_n for each product. I'd suggest combining the queries into something like this:

$sql = mysql_query("SELECT *, "
                  + "SUM(IF(trans_inventory>0,trans_inventory,0)) as pos, "
                  + "SUM(IF(trans_inventory<0,trans_inventory,0)) as neg "
                  + "FROM site_trans GROUP BY trans_product") or die(mysql_error());
while($row = mysql_fetch_array($sql)) {
    $amt_p = $row['pos'];
    $amt_n = $row['neg'];

    echo $amt_p;
    echo $amt_n;
}