Need Help with a SUM of a table calculation by group

jaustill

I am a novice with PHP, I am unable to determine how to get a SUM of a table calculation.

I have a table that has order numbers in field one and the item ordered and details about the item in fields 2 and 3 and 4 etc.

I am able to show [by a lot of help I got in this forum] the sum of all of the items in an order.

Here is my MSSQL table:

ORDER_NUMBER | ITEM_NUMBER | QUANTITY | PRICE |

67895 | BR124 | 1 | 2.00
67895 | FG536 | 1 | 2.00
67895 | M56 | 1 | 2.00
67895 | PFHR3 | 1 | 3.00
99678 | XC124 | 1 | 4.00
99678 | SF36 | 1 | 4.00
99678 | VM56 | 1 | 4.00
99678 | YYR3 | 1 | 5.00

I used this

SELECT order, sum(price)
FROM table
GROUP BY order_number;

to have PHP create a web table using the above table that shows the SUM of all of the items on each order like the following.

ORDER_NUMBER | TOTAL_ORDER_PRICE |

67895 | 9.00
99678 | 17.00

Now I would like to show a SUM of the "TOTAL ORDER PRICE" column or 26.00

bastien

just use a simple counter and add it in


//sum counter for totalling items
$total=0;

echo "<table><th>OrderNumber</th><th>Total Order Price</th>";

while ($rows=mysql_fetch_array($result)){

//not required by for explanation
//assing vars

$orderNo=$rows['order_number'];
$totalPrice=$rows['total_order_price'];

echo "<tr><td>$orderNo</td><td>$totalPrice</td></tr>";

   //add up everything
   $total+=$totalPrice;

} //end while

//add a blank line with a horizontal line in the second cell
echo "<tr><td>$nbsp;</td><td><hr></td></tr>";

//show the total
echo "<tr><td>Total (all orders)</td><td><b>$total</b></td></tr>";

hth

tekky

SELECT order, sum(price)
FROM table
GROUP BY order_number;

you could likewise do this...

SELECT sum(price) FROM table
....

that simple 🙂

bastien

true

but that involves another query...this doesn't

cabcree

bastien wrote:


//sum counter for totalling items
$total=0;

echo "<table><th>OrderNumber</th><th>Total Order Price</th>";

while ($rows=mysql_fetch_array($result)){

//not required by for explanation
//assing vars

$orderNo=$rows['order_number'];
$totalPrice=$rows['total_order_price'];

echo "<tr><td>$orderNo</td><td>$totalPrice</td></tr>";

   //add up everything
   $total+=$totalPrice;

} //end while

//add a blank line with a horizontal line in the second cell
echo "<tr><td>$nbsp;</td><td><hr></td></tr>";

//show the total
echo "<tr><td>Total (all orders)</td><td><b>$total</b></td></tr>";

Okay, I found this very helpful, but I have a question...the sprintf doesn't seem to work with this: $total+=$totalPrice;. Is there a reason? Is there another way to make sure that there are two numbers after the decimal?

cabcree

okay, I used this and it worked

<td align=\"right\">" . sprintf('%01.2f', $total_cart) . "</td>