SUM of multiple rows under foreach loop in PHP

Question

suppose, I have saved all the product codes in the table named as code_table, I am here not representing that table.

I am trying to get sum() of the LAST INSERT rate column rows. in the below code I have used, but I couldn't get the output as expected. Please help me how I get the all product SKU rate under foreach loop.

I have read all the threads but it all was not helpful for me.

TABLE = test

ID | product_sku | rate | code
-------------------------------
1  | 1230        | 23   | 111 
-------------------------------
2  | 1230        | 25   | 111  //COUNT THIS ROW ONLY
-------------------------------
3  | 1231        | 20   | 222 
-------------------------------
4  | 1231        | 26   | 222  //COUNT THIS ROW ONLY

Total SUM WILL BE = 51 // ROW ID 2 + 4 AS 25 + 26 = 51

$sql = $con->prepare("SELECT * FROM `code_table`");
$sql->execute();
foreach ($result as $data => $value) {
    $stmt = $con->prepare("SELECT *, SUM(`rate`) AS `totalCost` 
                             FROM `test` 
                             WHERE `code` = :code");
    $stmt->execute(array(':code' => $value['product_code']));
    while ($data = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $sum = 0;
        $sum+= $data['totalCost']
    }
}

echo $sum; //expected result = 51

Your query is malformed. There are columns in the list of selected columns that neither are in the `GROUP BY` clause nor an argument to an aggregation function. Sadly instances of old MySQL versions or badly configured ones accept such erroneous queries. But the result can suddenly be funny. — sticky bit, Jul 20 '21 at 11:04
check: https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql you need to group them by 'code` and rank them to get the latest ones only. — Hisham, Jul 20 '21 at 11:04
Well you could write a more complicated query OR in the while loop make some sort of effort to only add up the last occurance of a `code` — RiggsFolly, Jul 20 '21 at 11:04
___Small Point___ Setting `$sum=0;` Inside the while loop is not a sensible thing to do !!! — RiggsFolly, Jul 20 '21 at 11:12
@Hisham I am getting the same result even I used group function too.. — Satyam Nagar, Jul 20 '21 at 11:29
Also worth a read: https://dev.mysql.com/doc/refman/8.0/en/example-maximum-column-group-row.html — CBroe, Jul 20 '21 at 11:33

score 1 · Answer 1 · answered Jul 20 '21 at 12:22

There's no need for PHP loops here - just get your query right.

You want the last inserted row for each code, so extract the IDs of those rows with

SELECT max(id) as id FROM myTable group by `code`;

Then join that back to the original table to pull those rows:

select sum(rate) from myTable 
    inner join (SELECT max(id) as id FROM myTable group by `code`) sq on (myTable.id = sq.id) ;

Lastly, INNER JOIN that to code_table to filter by the required code, and add up the result:

select sum(rate) from myTable
    inner join (SELECT max(id) as id FROM myTable group by `code`) sq on (myTable.id = sq.id) 
    inner join code_table on (myTable.code = code_table.code);

Demo: https://www.db-fiddle.com/f/6ReEYsoQdjHV2XCi8CbRAJ/2

Since there's no user data here there's no need to prepare the query - just run it:

$query = "select sum(rate) from myTable inner join (SELECT max(id) as id FROM myTable group by `code`) sq on (myTable.id = sq.id) inner join code_table on (myTable.code = code_table.code)";
$stmt = $con->query($query);

$data = $stmt->fetch();

$total = $data[0];  // 51

SUM of multiple rows under foreach loop in PHP

1 Answers1