get unique value from array and sum totals

Question

From this Array() I want to grab the values of amount_total, shipping and partner and total them up by specific partner. So for example the partner=>2665 should have a amount_total of 41.79 + 55.95 and so on please help. I don't want to do it through SQL because I need the data as it is as well.

Array
(
    [0] => Array
    (
        [amount_total] => 41.79
        [amount_shipping] => 4.99
        [amount_partner] => 14.8
        [partner] => 2665
    )
    [1] => Array
    (
        [amount_total] => 55.95
        [amount_shipping] => 19.96
        [amount_partner] => 11
        [partner] => 2665
    )
    [2] => Array
    (
        [amount_total] => 51.96
        [amount_shipping] => 7.98
        [amount_partner] => 23.98
        [partner] => 51754
    )
    [3] => Array
    (
        [amount_total] => 24.55
        [amount_shipping] => 4.99
        [amount_partner] => 5.67
        [partner] => 11513
    )
)

so im doing foreach($result as $res) { $partner[$res['partner']] = array ( 'amount_total'=> ? ); } — Yeak, Dec 27 '12 at 20:15

Michel Feldheim · Accepted Answer · 2012-12-27T22:26:00.170

You could use PHP to achieve this, but this won't be necessary. The database can do this much more efficiently

I assume your current query looks something like this

SELECT
    `amount_total`,
    `amount_shipping`,
    `amount_partner`,
    `partner`
FROM
    `salesLeads`
WHERE
    [..]

MySQL gives you nice aggregate functions like SUM you can use with GROUP BY

SELECT
    SUM( `amount_total` ) AS `amount_total`,
    SUM( `amount_shipping` ) AS `amount_shipping`,
    SUM( `amount_partner` ) AS `amount_partner`.
    `partner`
FROM 
    `salesLeads`
WHERE
     [..]
GROUP BY
     `partner`

in your PHP script you access this the same way but it has the final numbers grouped and summarized by partner already

e.g.

if ($result = $mysqli->query($query)) {

    while ($row = $result->fetch_assoc()) {
        print_r( $row );
    }

    $result->close();
}

EDIT

Because you wanted a PHP solution, which again is more efficient than querying twice:

$partnerSums = array();
while ( $row = $result->fetch_assoc() ) {
     if ( !array_key_exists( $row['partner'], $partnerSums ) {
          $partnerSums[$row['partner']] = $row;
     } else {
          $partnerSums[$row['partner']]['amount_total'] += $row['amount_total'];
          $partnerSums[$row['partner']]['amount_shipping'] += $row['amount_shipping'];
          $partnerSums[$row['partner']]['amount_partner'] += $row['amount_partner'];
     }
}

print_r( $partnerSums );

Didn't actually read the "don't want to do it in SQL because..", have a look at [fireeyedboys answer](http://stackoverflow.com/questions/14060560/get-unique-value-from-array-and-sum-totals/14061798#14061914) — Michel Feldheim, Dec 27 '12 at 22:11
this doesnt seem to work its not giving me the total for that sepcific partner — Yeak, Dec 28 '12 at 00:40
What's the result for your example partner `$partnerSums[2665]`? Please note that the code I suggested is intended to get you a solution, not for productive use. I know very little to nothing about how you connect to the database for example, result iteration might need adjusting to your adapter — Michel Feldheim, Dec 28 '12 at 01:27

score 0 · Answer 2 · answered Dec 27 '12 at 20:07

0

I think looping through this array and storing the totals in new array, along with checking in_array (more here) should be sufficient. All you need to do is every time you loop through an element, check if it's already in new array, and if it is - perform necessary mathematical operation

answered Dec 27 '12 at 20:07

Moseleyi

2,585
1
24
46

so im doing foreach($result as $res) { $partner[$res['partner']] = array ( 'amount_total'=> ? ); } – Yeak Dec 27 '12 at 20:14

score 0 · Answer 3 · answered Dec 27 '12 at 20:29

I would suggest something like this:

   $res['totals']=array();

foreach($result as $res)
{
    $partner = $result['partner'];
    $res['totals'][$partner]+=$result['total_amount'];
}

This way you get all the data you need in just one place: the partner array '$res'.

Hope it helps you.

get unique value from array and sum totals

3 Answers3

Linked