Inner join sum SQL

Question

I have been trying to do a SUM with inner join, however it shows each cell rather than adding them together and showing only in one cell, where am I going wrong?

SELECT SUM(trans) as 'Transactions',city as 'city' 
FROM account 
INNER JOIN branch 
ON branch.bID 
GROUP BY account.trans, branch.city;

Answer 1

Remove account.trans in Group by. Since you added account.trans, branch.city in group by it will show sum(trans) per trans and city.

If you want sum(trans) per city then add branch.city alone in group by

SELECT SUM(trans) as 'Transactions'
FROM account 
INNER JOIN branch 
ON branch.bID 
Group by branch.city;

Answer 2

Use the following query which will return the sum per city

SELECT SUM(trans) as 'Transactions', branch.city as 'city' 
FROM account 
INNER JOIN branch 
ON branch.bID = account.bID
GROUP BY branch.city;