SQL SUM GROUP BY two tables

Question

I'm having difficulty writing an SQL query that will correctly group account_no together and subtracting an amount.

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.

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    

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.

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   

The expected outcome should be:

ACCOUNT_NO| BALANCE   | AMOUNT    | CORRECTBALANCE 

A-102     |  4000     |  500      |  4500                 
A-222     |  8000     |  -1000    |  7000                   
A-305     |  2000     |  1300     |  3300    

This is because the account A-102 it has two different amounts coming out, but from the same balance.

Answer 1

For your query, to get the two rows grouped on one row, you can try grouping on the account number AND the balance:

SELECT  T.account_no
        ,A.balance
        ,SUM(T.amount) AS TotalAmount
        ,(A.balance + SUM(T.amount)) AS "CORRECT BALANCE"
FROM    transactions AS T
INNER JOIN account AS A ON T.account_no = A.account_no
GROUP BY T.account_no, A.balance;

(By the way, I've used the ANSI join instead of the 'old' way of joining tables, because it's much more clear what you're doing that way.)

EDIT

To make things a bit more clear, I've made a SQL Fiddle. Does this represent your situation more or less correctly?

EDIT2

The above query would not show any accounts without transactions, as Kaf commented. That might be what you want, but in case it's not you can switch the join tables like this:

SELECT  A.account_no
        ,A.balance
        ,SUM(T.amount) AS TotalAmount
        ,(A.balance + SUM(T.amount)) AS "CORRECT BALANCE"
FROM    account AS A
LEFT OUTER JOIN transactions AS T ON T.account_no = A.account_no
GROUP BY A.account_no, A.balance;

Answer 2

Are you looking how to join tables and sum using group by?

First query;

UPDATE: I think your Account table has 1:many relationship with Transaction table, so, you should get the sum from transaction table and then join with Account. Ideally, you need a left join as below.

select a.account_no, 
       a.balance, 
       isnull(x.amount,0) amount, 
       (a.balance + isnull(x.amount,0)) correctAmount
from account a left join (
           select t.account_no, sum(t.amount) amount
           from transactions t
           group by t.account_no ) x
   on a.account_no = x.account_no

SQL-FIDDLE-DEMO (thanks @Josien for tables and data)