CODEWITHSUNDEEP
phpmysql
cantaffordavan
cantaffordavan

Reputation: 1447

Remove negative results from SUM

I am using the following code below to group and sum stock for my inventory. For some of my stock I can get negative results if there are more shipments than inventory, but I would like for these negative quantity items not to show in the "stock page" I have. Is there a way to remove the rows that are equal or less than 0 for quantity?

-------------------------------------------------------------------
ProductID   |  Color  | Timestamp          |  Quantity  |  RowID  |
-------------------------------------------------------------------
25          |  Red    | 10/10/10 06:22:15  |  -250      |  1      |
-------------------------------------------------------------------
32          |  Green  | 10/10/10 06:23:45  |  100       |  2      |
-------------------------------------------------------------------

$query = "SELECT productid, color, SUM(Quantity) AS TotalQuantity FROM inventory GROUP BY productid, color";
$result = mysql_query($query) or die(mysql_error());

   // Table markup here
   echo '<tr><td>'. $row['productid'] . '</td><td>' . $row['color']. '</td><td>'. $row['TotalQuantity'];
   // Table markup here

Upvotes: 2

Views: 227

Answers (1)

Nir Alfasi
Nir Alfasi

Reputation: 53525

$query = "Select * from 
  (SELECT productid, color, 
   SUM(Quantity) AS TotalQuantity FROM inventory 
   GROUP BY productid, color) AS alias 
where TotalQuantity  > 0";

Upvotes: 3

Related Questions