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:


Code:
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:

Code:
111
5
10

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


Code:
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