[SOLVED] Best way to loop through an array and sum

[Jonob]

I have an array of arrays being returned from mysql as follows.

 

Array
(
    [0] => Array
        (
            [bank_id] => 1
            [bank_name] => Bank 1
            [total_amount] => 100.00
        )

    [1] => Array
        (
            [bank_id] => 2
            [bank_name] => Bank 2
            [total_amount] => 0.00
        )

    [2] => Array
        (
            [bank_id] => 1
            [bank_name] => Bank 1
            [total_amount] => 600.00
        )

    [3] => Array
        (
            [bank_id] => 2
            [bank_name] => Bank 2
            [total_amount] => 300.00
        )

    [4] => Array
        (
            [bank_id] => 3
            [bank_name] => Bank 3
            [total_amount] => -200.00
        )

    [5] => Array
        (
            [bank_id] => 2
            [bank_name] => Bank 2
            [total_amount] => -10
        )

)

 

What is the easiest way for me to loop through and return an array of unique [bank_id], [bank_name] and sum([total_amount]) for each bank_id?

 

Using the above figures, I would like to return:

Array
(
    [0] => Array
        (
            [bank_id] => 1
            [bank_name] => Bank 1
            [sum] => 700.00
        )

    [1] => Array
        (
            [bank_id] => 2
            [bank_name] => Bank 2
            [sum] => 290.00
        )
    [2] => Array
        (
            [bank_id] => 3
            [bank_name] => Bank 3
            [sum] => -200.00
        )
)

 

Thanks for any help you can provide.

[PFMaBiSmAd]

What is the easiest way for me to ... return an array of unique [bank_id], [bank_name] and sum([total_amount]) for each bank_id?

 

Do it in your query. Use GROUP BY bank_id to consolidate all the rows for each bank_id and use a SUM(total_amount) to produce the sum for each resulting group.

[xaeryan]

Perform as mentioned above, here's a quick example:

 

select bank_id, bank_name, SUM(total_amount) as [sum] from bank_amounts_table GROUP BY bank_id, bank_name

 

If bank_name is in a separate table from bank_amounts_table (which it should be), you might have something like this:

 

select b.bank_id, n.bank_name, SUM(b.total_amount) as [sum] from bank_amounts_table b

INNER JOIN bank_name_table n ON n.bank_id = b.bank_id

GROUP BY b.bank_id, n.bank_name

 

[Jonob]

Thanks for the replies.

 

To be honest, that was my original approach, but I ran into a wall there...the reason being the data comes from 5 different tables.

 

Maybe I am approaching this all wrong, but here is my query:

 

SELECT b.bank_id, b.bank_name, IFNULL(ROUND(b.start_bal,2),0) as total_amount
						FROM bank b
						WHERE b.is_deleted = '0' AND b.company_id = 1 AND b.bank_type_id<4 AND b.is_deleted='0'			

		UNION
		(SELECT i.bank_id, b.bank_name, ROUND(SUM(id.amount_in),2) as total_amount
			FROM income_detail id 
			LEFT JOIN income i ON i.income_id = id.income_id
			LEFT JOIN bank b on i.bank_id = b.bank_id
			WHERE i.is_deleted = '0' AND b.company_id = 1 AND b.bank_type_id<4 AND b.is_deleted='0'
			group by i.bank_id)
		UNION 
		(SELECT e.bank_id, b.bank_name, -ROUND(SUM(e.amount_in),2) as total_amount
			FROM expense e 
			LEFT JOIN bank b on e.bank_id = b.bank_id
			WHERE e.is_deleted = '0' AND b.company_id = 1 AND b.bank_type_id<4 AND b.is_deleted='0'
			group by e.bank_id)
		UNION
		(SELECT a.bank_id, b.bank_name, -ROUND(SUM(a.amount_in),2) as total_amount
			FROM asset a 
			LEFT JOIN bank b on a.bank_id = b.bank_id
			WHERE a.is_deleted = '0' AND b.company_id = 1 AND b.bank_type_id<4 AND b.is_deleted='0'
			group by a.bank_id)
		UNION
		(SELECT a.bank_id, b.bank_name, ROUND(SUM(a.amount_in),2) as total_amount
			FROM asset_disposal a 
			LEFT JOIN bank b on a.bank_id = b.bank_id
			WHERE a.is_deleted = '0' AND b.company_id = 1 AND b.bank_type_id<4 AND b.is_deleted='0'
			group by a.bank_id)

 

And no, these tables cannot be combined into one big table; they are normalised and in separate tables for a good reason.

 

If, however, there is an easier way to get the sum, then I'd love to hear it.

[printf]

Use a UNION ALL, enclose all the queries using UNION ALL

 

SELECT a, b, SUM(IFNULL(ROUND(c,2),0)) FROM
((SELECT a, b, c FROM table...) 
UNION ALL 
(SELECT a, b, c FROM table...) 
UNION ALL 
(SELECT a, b, c FROM table...) 
UNION ALL 
(SELECT a, b, c FROM table...) 
UNION ALL 
(SELECT a, b, c FROM table...)) AS junk

[Jonob]

Ahh, thats awesome, thank you.

 

The only bit that you left out was

GROUP BY a 

at the end

 

 

[jammesz]

This will get the result you want...

 

<?
$i_array = array(
array('bank_id'=>1,'bank_name'=>1,'total_amount'=>100),
array('bank_id'=>2,'bank_name'=>2,'total_amount'=>0),
array('bank_id'=>1,'bank_name'=>1,'total_amount'=>600),
array('bank_id'=>2,'bank_name'=>2,'total_amount'=>300),
array('bank_id'=>3,'bank_name'=>3,'total_amount'=>-200),
array('bank_id'=>2,'bank_name'=>2,'total_amount'=>-10)
);

$o_sum_array=array();
$o_array=array();
foreach($i_array as $ia){
$o_sum_array[$ia['bank_id']] += $ia['total_amount'];
$o_array[$ia['bank_id']] = array('bank_id'=>$ia['bank_id'],'bank_name'=>$ia['bank_name'],'sum'=>$o_sum_array[$ia['bank_id']]);
}

echo '<pre>';
print_r($o_array);
echo '</pre>';
?>

 

Result...

 

Array
(
    [1] => Array
        (
            [bank_id] => 1
            [bank_name] => 1
            [sum] => 700
        )

    [2] => Array
        (
            [bank_id] => 2
            [bank_name] => 2
            [sum] => 290
        )

    [3] => Array
        (
            [bank_id] => 3
            [bank_name] => 3
            [sum] => -200
        )

)