mysql分列求和_MySQL：按两列分组并求和

Building an inventory system. I have lots of products and each product has three different variables. So for stock totals I want to group by the two columns (product & size) and sum quantity to get stock total.

---------------------------------

|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 sum using the code below:

$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 "
";

}

?>

I am just stuck on grouping by both columns. Is it possible? or should I just create three different products for the of the three sizes and eliminate that column? Thanks.

解决方案

Based on your example table, it appears you want to be grouping on product rather than id. You merely need to add the Size column to both the SELECT list and the GROUP BY

$query = "SELECT

product,

Size,

SUM(Quantity) AS TotalQuantity

FROM inventory

GROUP BY product, Size";

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