# subtracting two diff columns

• Dec 9, 2008, 05:41
phantom007
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:

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
• Dec 9, 2008, 05:53
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.

Code MySQL:

```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```
• Dec 9, 2008, 05:54
guido2004
Joining these two tables on city, will give the following result:

Code:

```111  6 111  5   5  6   5  5  10  6  10  5```
This gives 252 - 33 = 219
• Dec 9, 2008, 05:57
phantom007
Quote:

Originally Posted by guido2004
Joining these two tables on city, will give the following result:

Code:

```111  6 111  5   5  6   5  5  10  6  10  5```
This gives 252 - 33 = 219

Wots the solution then?
• Dec 9, 2008, 06:00
phantom007
Quote:

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.

Code MySQL:

```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```

What I am doing is incorrect and there is a way?

OR

Is it something not possible in one query?

Thanx
• Dec 9, 2008, 06:02
r937
Quote:

Originally Posted by cancer10
Wots the solution then?

obviously, the solution is: don't join

Quote:

Originally Posted by cancer10
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.

Code:

```SELECT ( SELECT SUM(Amount) FROM tableA )     - ( SELECT SUM(Amount) FROM tableB ) AS difference```
:cool:
• Dec 9, 2008, 06:08
phantom007
Quote:

Originally Posted by r937
obviously, the solution is: don't join

Code:

```SELECT ( SELECT SUM(Amount) FROM tableA )     - ( SELECT SUM(Amount) FROM tableB ) AS difference```
:cool:

That worked, but can it also show the cities also incase there are more then one city?
• Dec 9, 2008, 06:12
r937
i'm sorry, that solution works for the question you posted

if you change the question, then the solution has to change too -- obviously

please explain what you mean by "more than one city"
• Dec 9, 2008, 06:17
phantom007
Quote:

Originally Posted by r937
i'm sorry, that solution works for the question you posted

if you change the question, then the solution has to change too -- obviously

please explain what you mean by "more than one 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.

Thanx

EDIT: So that it looks like

City | Difference
---------------------
City A | 55
City B | 22
City C | 543
City D | 212
• Dec 9, 2008, 06:41
r937
Quote:

Originally Posted by cancer10
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.

Code:

```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```