title: SQL SUM GROUP BY two tables
body: <p>I'm having difficulty writing an SQL query that will correctly group account_no together and subtracting an amount.</p>
  
  <p>Firstly I wrote this query which updates everything fine except ACCOUNT_NO A-102 should end up as 4500 not as two different correct balances.</p>
  
  <pre><code>select transactions.account_no, account.balance, transactions.amount,
  (account.balance + transactions.amount) AS "CORRECT BALANCE"
  from transactions, account
  where account.account_no = transactions.account_no;
  
  ACCOUNT_NO| BALANCE   | AMOUNT    | CORRECTBALANCE 
  
  A-102     |  4000     |  2000     |  6000                   
  A-102     |  4000     |  -1500    |  2500                   
  A-222     |  8000     |  -1000    |  7000                   
  A-305     |  2000     |  1300     |  3300    
  </code></pre>
  
  <p>I tried to sum and group by the account_no but I cannot work out how to do this with the two tables. This was just something I tried but could not get to work.</p>
  
  <pre><code>select transactions.account_no, SUM(transactions.amount)
  from transactions
  group by transactions.account_no;
  
  
  ACCOUNT_NO| SUM(TRANSACTIONS.AMOUNT) 
  A-305     |    1300                     
  A-102     |    500                      
  A-222     |    -1000   
  </code></pre>
  
  <p>The expected outcome should be:</p>
  
  <pre><code>ACCOUNT_NO| BALANCE   | AMOUNT    | CORRECTBALANCE 
  
  A-102     |  4000     |  500      |  4500                 
  A-222     |  8000     |  -1000    |  7000                   
  A-305     |  2000     |  1300     |  3300    
  </code></pre>
  
  <p>This is because the account A-102 it has two different amounts coming out, but from the same balance.</p>