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.

    The subtotals can be done using SUM+GROUP BY in the db (and it would be easier to do there).

    The grand total might be possible to do in the db as part of the same query, but I'm not too sure, and wouldn't consider it anyway: since you are already fetching the subtotals into the app you might as well use those to calculate the grand total there.

    In fact, since you're fetching all the information you need to calculate the line totals for its own sake already, you could do the calculations for those in the application as well.

    I'm not 100% sure about the setup of your query; I'm pretty certain most DBMSs would refuse to run it (not all ungrouped fields are aggregated), but MySQL is lazy that way. I guess if it works that's good enough.

      Without sounding too dense, by running in the app do you mean use the array_sum method?

      And, yes my mySQL skills definitely need help. I'll have another look at the query before putting it into production.

      Thanks.

        gwerner wrote:

        Without sounding too dense, by running in the app do you mean use the array_sum method?

        Yep.

          Write a Reply...