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

You are getting Table A summed more than once because of your join. get rid of the subtraction of the sum()s and just print sum(a) and sum(b) and run the query to see how the joins are causing things to be double counted.

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

See, I might have multiple cities in both tableA and tableB.

I want to do a sum of all rows for column “Amount” for each city in tableA and then do a sum of all rows for column “Amount” for each city in tableA
then subtract the amount from both tables city wise.

SELECT a.city
, a.total
, b.total
, a.total - b.total AS difference
FROM ( SELECT city
, SUM(amount) AS total
FROM tableA
GROUP
BY city ) AS a
LEFT OUTER
JOIN ( SELECT city
, SUM(amount) AS total
FROM tableB
GROUP
BY city ) AS b
ON b.city = a.city