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.

Could two equal account numbers occur with a different balance, eg. "A-102" with balance 4000 and "A-102" with balance 3200? — Josien, Feb 27 '13 at 10:45
No, the balance is meant to be exactly the same. It comes up twice because in the table it's a joint-account so it's in for two different names. It's always meant to be exactly the same. — Sjrsmile, Feb 27 '13 at 10:49
I get it. Have you taken a look at my solution for your query? — Josien, Feb 27 '13 at 10:54

score 7 · Accepted Answer · edited May 23 '17 at 12:32

7

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;

edited May 23 '17 at 12:32

Community

1
1

answered Feb 27 '13 at 10:11

Josien

13,079
5
36
53

@Sjrsmile: I am not worried you selecting this answer, but according to your table structure, I think best thing is to get the sum from transaction table before joining to avoid unwanted grouping. That was why I removed same answer from my post.. Also, this query won't give you any records for accounts with no records in transaction table. – Kaf Feb 27 '13 at 11:46
@Kaf: you're absolutely right about the records without transactions. I'll add a query that will take care of that (basically switching the join order). As to the sum-before-joining I'm not sure if that is a problem. Can you think of an example where this would go wrong? – Josien Feb 27 '13 at 15:04
Not wrong, but I think can avoid unwanted grouping by summing first and then joining. Anyways, he has got what he needed in either way and disappeared.. – Kaf Feb 27 '13 at 15:11
1

True... Ah, the hardships of the unloved StackOverflow Army of Volunteers... :-) – Josien Feb 27 '13 at 20:20

Kaf · Answer 2 · 2013-02-27T11:17:12.580

3

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)

edited Feb 27 '13 at 11:17

answered Feb 27 '13 at 10:09

Kaf

33,101
7
58
78

Getting ahead of myself, apologies. That nearly works, but it's also summing up the balance for A-102 twice, while the balance is only 4000, not 8000.
The second query was just showing how I was trying to solve it myself but was having no luck going any further forward. – Sjrsmile Feb 27 '13 at 10:32
I've edited my first comment instead of saying a response, hadn't realised I could add more comments ~ sorry! This also sums up the balance to 8000 while it just needs to stay at 4000, is there a way to ony sum up the amount and leave the balance alone? – Sjrsmile Feb 27 '13 at 10:36
1

From the given data: A-102 has two records each 4000 balance. When the sum total = 4000+4000 > 8000? can you update the question with expected results? – Kaf Feb 27 '13 at 10:38
You could try grouping on the account.balance as well in the first query. That way you're not adding up the balances. – Josien Feb 27 '13 at 10:40
@Josien, for the given data that would be okay, but how about if he had another record for A-102 with balance 3000? I think something is not clear in his question... – Kaf Feb 27 '13 at 10:42
Thanks for being patient, I've updated the question. I hope this makes it more clear? – Sjrsmile Feb 27 '13 at 10:44
1

@Sjrsmile: I have updated the answer, by looking at your expected results, I think you need `balance + sum(t.amount)`. But I am not sure why I would group by balance. – Kaf Feb 27 '13 at 10:49
That's perfect, thank you both for your help. I'm sorry I couldn't be more clear, but you've done me a service non-the-less. Thanks! – Sjrsmile Feb 27 '13 at 10:56
1

@Sjrsmile, don't take the first query, it will work only for the given data. Second updated query is the correct one because your account table has 1 to many relation ship with transaction table, so, you should get the sum from transaction and then join with account. – Kaf Feb 27 '13 at 11:00
1

+1, though it seems like a much more convoluted solution than mine :-) – Josien Feb 27 '13 at 11:06
@Josien, I new there was some piece of information missing. Thanks for your fiddle... I'll give you +1 for doing that without much info. – Kaf Feb 27 '13 at 11:09
I tried this, but it throws an error "FROM keyword not found where expected" Error on line 3. – Sjrsmile Feb 27 '13 at 11:11
2

I take that back, what I've tried now works. Excellent work guys! – Sjrsmile Feb 27 '13 at 11:16

SQL SUM GROUP BY two tables

2 Answers2

Linked