subtracting two diff columns

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

Code (markup):

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

111
5
10

Code (markup):

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

6
5

Code (markup):

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

 

Cancer10,

I had a little trouble understanding your original query, but do get the exact same results using your data and query. I was able to achieve the desired results with the following on MySQL 5.0.67:

SELECT city,sum(totalAmount) AS amount 
  FROM (
    (SELECT city,sum(amount) AS totalAmount FROM tableA GROUP BY city)
  UNION
    (SELECT city,sum(amount * -1) AS totalAmount FROM tableB GROUP BY city)
  ) AS totalsTable WHERE city='Hyderabad' GROUP BY city;

Code (markup):

The data I used was:

mysql> select * from tableA;
+-----------+--------+
| city      | amount |
+-----------+--------+
| Hyderabad |    111 | 
| Hyderabad |      5 | 
| Hyderabad |     10 | 
| Rochester |     15 | 
+-----------+--------+

Code (markup):

mysql> select * from tableB;
+-----------+--------+
| city      | amount |
+-----------+--------+
| Hyderabad |      6 | 
| Hyderabad |      5 | 
| Rochester |      7 | 
+-----------+--------+

Code (markup):

In this case I got 115 for Hyderabad and changing the WHERE to 'Rochester' also correctly returned "8" (15-7=8).

Hopefully this helps!

 

Thanx for your reply