CODEWITHSUNDEEP
phpmysql
Archie Zineg
Archie Zineg

Reputation: 145

SUM within a loop - MYSQL

I have those tables:

Table alltransations

enter image description here

Table alladdon_list

enter image description here

I just want to simple sum up all addons with specific transac_id and group it by guest_id so I can get the result as this:

enter image description here

But with my query:

<table border=1 cellpadding=5 >
    <tr>
        <th>Names</th>
        <th>Amount</th>
    </tr>

<?php

include("conn_archive.php");

$cfaddontotal = 0;

$getall = mysql_query("SELECT *, sum(quantity) as quantity,price
                        FROM alltransactions at, alladdon_list aal
                        WHERE at.transac_id=aal.aatransac_id
                        GROUP BY guest_id
                        ", $db2);
while($rows = mysql_fetch_assoc($getall)){
        // GUEST INFO
        $guest_fname = $rows['guest_fname'];
        $guest_lname = $rows['guest_lname'];

        // ADDON INFO
        $addondesc = $rows['description'];
        $addondate = $rows['datef'];
        $addonqty = $rows['quantity'];
        $addonprice = $rows['price'];
        $addontcost = $addonprice * $addonqty; // cost of specific product
        $cfaddontotal += $addonprice * $addonqty; // total cost of all products

        echo "<tr>";
        echo "<td>$guest_fname $guest_lname</td>";
        echo "<td>&#8369; ".number_format($addontcost)."</td>";
        echo "</tr>";
}

?>
<tr>
    <th colspan=2 >Total Addon Cost: &#8369; <?php echo number_format($cfaddontotal); ?></th>
</tr>
</table>

I got this(which is wrong):

enter image description here

Can't figure out what query to use to get the accurate computations.

Upvotes: 1

Views: 1288

Answers (1)

sectus
sectus

Reputation: 15464

Your code is confusing. Your are summing quantity of all products(with different prices) and multiplying by one "random" price (it would be good if select with group by did not contain fields without aggregate functions: MySQL Handling of GROUP BY).

It's better to change your query to something

SELECT *, sum(quantity*price) as total
                    FROM alltransactions at, alladdon_list aal
                    WHERE at.transac_id=aal.aatransac_id
                    GROUP BY guest_id

Upvotes: 2

Related Questions