CODEWITHSUNDEEP
phpmysql
user2465936
user2465936

Reputation: 1040

Sum values in one mysql column and group by values of other column (using php foreach)

Started to write question, but while wrote question, get answer. So not to delete all content, post the answer (possibly may be useful). And also may be there is something to improve.

Based on this https://stackoverflow.com/a/15048890/2465936 answer trying to sum values in one column and group by values in other column

For example, table named 2_1_journal

TransactionPartnerName | Amount
-------------------------------
Name one               |   1
Name two               |   2
Name one               |   3
Name three             |   10
Name two               |   7
Name one               |   150

As result want to get this

Name one = 154
Name two = 9
Name three = 10

Upvotes: 0

Views: 1540

Answers (3)

Ujang Hardman
Ujang Hardman

Reputation: 115

It's better if you create a view of your table.

To Create a View:

  1. go to your 2_1_journal table,
  2. In the bottom of your table, next to "Display Chart", click "Create View" enter image description here
  3. Then You'll See This Display enter image description here
  4. In The View name, fill it up to you (e.g: view_2_1_journal)
  5. In The AS, fill it with this (OR Like in the picture above):

enter image description here

  1. After That, Your view_2_1_journal successfully created.

  2. view_2_1_journal will be like this:

    TransactionPartnerName | SUM(`Amount`)
--------------------------------------
Name one               |   154
Name two               |   9
Name three             |   10

  3. To Show it on the php, Use Query Select like you always do, the different is to show the amount, insted of use (for example) something like this: $show['Amount'], use this enter image description here, instead.

Hope this help.

Upvotes: 0

Orangepill
Orangepill

Reputation: 24665

If you are getting from a database structuring your query to give you this information is almost always better but if that is off the table you can use this.

$res = array();
foreach($array as $row){
   // make sure we don't get E_NOTICE's when accessing element for the first time
   if (!isset($res[$row["TransactionPartnerName"]])) $res[$row["TransactionPartnerName"]] =0;
   // Use the array key as a means to group
   $res[$row["TransactionPartnerName"]]+= $row1['SUM(Amount)'];
}

echo "<table>";
echo "<thead><tr><th>Transaction Partner</th><th>Total</th></tr></thead>";
echo "<tbody>";
foreach($res as $k=>$v){
    echo "<tr><td>".htmlspecialchars($k)."</td><td>".$v</td></tr>";
}
echo "</tbody>";
echo "</table>";

Upvotes: 0

user2465936
user2465936

Reputation: 1040

MySQL query 

$query_select_all ="
SELECT TransactionPartnerName, SUM(Amount) 
FROM 2_1_journal
GROUP BY TransactionPartnerName";

Then

$sql = $db->prepare($query_select_all);
$sql->execute();
$sql = $sql->fetchAll();

With print_r($sql); get

Array ( [0] => Array ( [TransactionPartnerName] => name one [0] => name one [SUM(Amount)] => 154.00 [1] => 154.00 ) [1] => Array ( [TransactionPartnerName] => name three [0] => name three [SUM(Amount)] => 10.00 [1] => 10.00 ) [2] => Array ( [TransactionPartnerName] => name two [0] => name two [SUM(Amount)] => 9.00 [1] => 9.00 ) )

So far seems ok.

Then foreach

<?php
foreach ($sql as $i1 => $row1) {
?>
<tr>
<td width="90px"><div style="width:90px;">
<?php echo htmlspecialchars($row1['TransactionPartnerName'])?>
</div></td>
<td width="50px"><div style="width:53px;">
<?php echo htmlspecialchars($row1['SUM(Amount)'])?>
</div></td>
</tr>
<?php
}
?>

Upvotes: 2

Related Questions