sql SUBTRACTING TWO SUMS

[cli]

HI !

I am trying to subtract the sum of two different columns on two different tables from each other. If I write my sql as:

Select sum(table1.column1- table2.column2)
from table1, table2;

I get the wrong answer it subtracts the columns before summing them.

So now I have created a stored procedure which brings the results to two different columns in the results table. Now I am trying to write SQL to subtract the two columns from each other in the results table ie

Select tbresults.result1 - tbresults.result2
from tbresults;

but this doesn't work

I am looking for a way to either subtract one column from the other or else a way to solve the inital problem (subtracting two different column sums) PLEASE !!!

 

[Cagliostro]

how about?:
Select (sum(table1.column1)- sum(table2.column2)) as x
from table1, table2; John Fill

ivfmd@mail.md

 

[cli]

Hi ,


Have tried this but unfortunately it gives the same answer as

select sum (table1.column1 - table2.column2)
from table1, table2;

for some reason doing the way you suggested subtracts before it sums...

any other ideas ???

 

[carp]

The order of operation is not the problem here - the sum of differences is the same as the difference of sums.

The problem you are running into is the result of a cartesian join, caused by the fact that you have no "WHERE" clause in your query.

If you cannot come up with a way to join the rows between tables, you might try this:

SELECT x - y
FROM (SELECT sum(column1) x FROM table1),
(SELECT sum(column2) y FROM table2);

 

[tlbroadbent]

As carp said, the problem isn't in the SUM. The problem is that the query is a cross join that joins every row in table1 to every row in table2. You need to add a join criteria.

Select sum(table1.column1- table2.column2)
from table1 join table2 On tabl1.ID=table2.ID;

Terry

http://members.home.net/tlbroadbent/prog.htm

------------------------------------
Experience is the hardest kind of teacher. It gives you the test first, and the lesson afterward.

 

[link9]

Or, can't you just say:

SELECT ((SELECT SUM(column1) FROM table1) - (SELECT SUM(column2) FROM table2)) AS theSum

Two different queries, and just subract the results and smack an alias on it.


Paul Prewett

 

[kittyyo]

As I have the same problem, but am using MySQL Version < 4.1:

Is this

SELECT ((SELECT SUM(column1) FROM table1) - (SELECT SUM(column2) FROM table2)) AS theSum

also possible without using subqueries?