I'm not sure if I'm going about this the right way. I would like to be able to get the overall SUM total of orders placed for the query below. I've been trying let mySQL handle the equation vs putting the returned data in an array and then doing an array_sum. Is this even possible with mySQL, or should I just use the array method? The array method is tons easier, but not sure if it will slow the system over time?
There are two tables. The first contains order information such as name, date ordered, etc. The second table contains the items purchased and has _oid to link it back to the first table.
Second part of this equation is that I accept two different order types, personal and business. With the query below I would like to get the grand SUM of either all the personal or business, but not together. The second table does not contain any information regarding the order type.
My query so far looks like this which gives me the total for all the orders (totalSum):
<?php
$query1 = "SELECT orderinfo_id, orderinfo_date, orderitems_quantity, orderitems_price, ROUND(SUM(orderitems_price * orderitems_quantity), 2) AS totalPrice, SUM(orderitems_quantity) AS totalItems, (SELECT ROUND(SUM(orderitems_price * orderitems_quantity), 2) FROM orderitems)AS totalSum
FROM orderinfo
LEFT JOIN orderitems ON orderinfo.orderinfo_id = orderitems.orderitems_oid
WHERE orderinfo_coid = '$companyId' AND date_format(`orderinfo_date`, '%m') = '$monthId' AND orderinfo_approval = '0'
GROUP BY orderinfo_id";
$result1 = mysql_query($query1);
while($row1 = mysql_fetch_assoc($result1)) {
extract($row1);
?>
I then run the query in a while loop to spit out results. I can get line by line the totals for each individual order, but not the total of all the orders.
<tr>
<td>Total 1</td>
<td>Total 2</td>
<td>Total 3</td>
</tr>
<tr>
<td>GRAND TOTAL</td>
</tr>
Please help or give guidance. Thanks.