Adding the sum of another table column to a query

Question

I am using following query to get all bank accounts from a database table (tb_bank_accounts). There is another table called tb_movements, and I need to add as new column in the query the SUM of the column 'income' from all records from the table tb_movements where the column 'bank' is equal to the column id_bank_account in the query.

$query="SELECT * from tb_bank_accounts LEFT JOIN tb_currencies ON tb_bank_accounts.currency = tb_currencies.id_currency";
$result = $mysqli->query($query) or die($mysqli->error.__LINE__);

$arr = array();
if($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $arr[] = $row;  
    }
}
# JSON-encode the response
$json_response = json_encode($arr);

// # Return the response
echo $json_response;

I guess I should use another LEFT JOIN to do it...

Gordon Linoff · Accepted Answer · 2016-03-25 01:03:54Z

1

It might be easiest to do using a correlated subquery. Something like this:

SELECT a.*, c.*,
       (SELECT SUM(m.income)
        FROM movements m
        WHERE m.bank_id = a.bank_id
       ) as income
from tb_bank_accounts a LEFT JOIN
     tb_currencies c
     ON a.currency = c.id_currency;

answered Mar 25, 2016 at 1:03

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding the sum of another table column to a query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related