MySQL - Sum from another table

mysql, sql

Solution

Try

SELECT account_name, SUM(balance) balance
 FROM tblaccounts a LEFT JOIN tblinvoices i
   ON a.account_id = i.tblaccount_account_id
WHERE a.customer_id = 1
GROUP BY account_id

Output:

| ACCOUNT_NAME | BALANCE |
--------------------------
|     Account1 |      50 |
|     Account2 |     100 |

Here is SQLFiddle demo

Problem

I want to run a select statement that includes a sum from another table. I know I will probably have to setup a join but I'm not sure how to approach it. I want to select a list of accounts then get their balances(sum from another table) This is the idea I have been going in but obviously its not correct. ``` SELECT account_name FROM tblaccounts JOIN ( SELECT SUM(balance) AS account_balance FROM tblinvoices ) t ON t.account_id = tblaccount_account_id WHERE tblaccounts.account_id = 1 ``` desired output ``` Name | balance ------------------ Account1 50 Account2 100 ```

Original source