Reputation: 1447
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:
product | Size | Quantity |
---|---|---|
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 "<br />";
}
?>
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.
Upvotes: 5
Views: 26372
Reputation: 270795
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)']
Upvotes: 11