MySQL: two-column group and sum

Question

Creating an inventory system. I have many products, and each product has three different variables. Thus, for the totals, I want to group by two columns (product and size) and the amount amount in order to get the total amount.

--------------------------------- |product |Size |Quantity | --------------------------------- |Widget one |2 |275 | --------------------------------- |Widget one |2 |100 | --------------------------------- |Widget two |3 |150 | --------------------------------- |Widget two |2 |150 | --------------------------------- 

What I want for output:
Widget One - 2: 375
Widget two - 3: 150
Widget two - 2: 150

I figured out how to group by one column and summarize using the following code:

 $query = "SELECT product, SUM(Quantity) FROM inventory GROUP BY product"; $result = mysql_query($query) or die(mysql_error()); // Print out result while($row = mysql_fetch_array($result)){ echo "Total ". $row['product']. " = ". $row['SUM(Quantity)']; echo "<br />"; } ?> 

I was just stuck in grouping on both columns. Is it possible? or should I just create three different products for three sizes and exclude this column? Thanks.

Answer 1

Based on your example table, you want to group product , not id . You just need to add the Size column to the SELECT and GROUP BY list

 $query = "SELECT product, Size, SUM(Quantity) AS TotalQuantity FROM inventory GROUP BY product, Size"; 

Note that I have added a TotalQuantity column TotalQuantity , which will allow you to more easily retrieve a column from the extracted row using the more reasonable $row['TotalQuantity'] rather than $row['SUM(Quantity)']