subtracting two diff columns

[cancer10]

Hi

I am confused how to do this.

I have two tables and each table has an "Amount" colum.


I need to sum up all the rows for column "Amount" in table A and sum up all the rows for column "Amount" in table B

and then subtract those two colums.

Here is what I am doing:



select tableA.city, sum(tableA.amount) - sum(tableB.amount)
from tableB,tableA
where
tableB.city='Hyderabad' and
tableB.city=tableA.city
group by tableB.city



Say: tableA Has the following data for column "Amount" and city=Hyderabad:


111
5
10


AND tableB Has the following data for column "Amount" and city=Hyderabad:



6
5



Now, my above query should do the following:

Sum all amounts for tableA, which comes to 126
Sum all amounts for tableB, which comes to 11


So 126 - 11 = 115

But I get the result 219

Any idea wots going on and how to fix it?


Thanx

[Fumigator]

You have to be mindful whenever you use GROUP BY, and you certainly can't use a column function (sum) on a column not in the GROUP BY, especially on a column in a joined table.

You may want to try using a subquery:


SELECT tableA.city
,SUM(tableA.amount) - (SELECT SUM(tableB.amount) FROM tableB
WHERE tableB.city = tableA.city)
FROM tableA
WHERE tableA.city = 'north pole'
GROUP BY tableA.city


p.s. FYI, it's spelled "what". You have no idea how uneducated you look when you use "wot". I'm just trying to help you out here. I don't enjoy being a spelling nazi but this one particular misspelling is horrendous.