CODEWITHSUNDEEP
phpmysql
mvasco
mvasco

Reputation: 5101

Adding the sum of another table column to a query

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...

Upvotes: 0

Views: 140

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

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;

Upvotes: 1

Related Questions