Problem: getting different results from SUM queries

Question

When I ran the following query:

select SUM(t1.total_amount) as one, SUM(t2.total_amount) as two from table1 t1, table2 t2; 

I get the following results:

 ONE TWO 2000 3000 

But when I run this query:

 select SUM(t1.total_amount) as one table1 t1; 

I get this result:

 ONE 50 

It seems that the result of the first query is incorrect. Can someone point me in the right direction?

Answer 1

Wherein:

 select * from table1 t1, table2 t2 

you actually intersect both tables, which results in a Cartesian product (each row in t1 is combined with each row in t2).

You probably have no JOIN condition:

 select sum(t1.total_amount), sum(t2.total_amount) from t1 join t2 on t1.[???] = t2.[???] 

EDIT:

based on your comment, it looks like you want to combine these two separate queries select 't1', sum (total_amount) from t1 union select 't2', sum (total_amount) from t2

This displays the sums in two rows instead of columns, but this is the easiest way AFAIK.

Answer 2

Hmmm, did you answer a Google question before writing a question? Google "+ oracle + combining tables" gives a good answer right now: Oracle / SQL - Combining counts from "unrelated" unrelated tables

Since you indicate that the table is unrelated, just with similar data, UNION is probably the best solution. But in accordance with the above question, you can select partial results, and then combine them with the desired donation to “choose from a double” (non) context.

Example:

 select COUNT(*), SUM(X) from SOME_TABLE; COUNT(*) SUM(X) ---------- ---------- 57 6450 select COUNT(*), SUM(X) from OTHER_TABLE; COUNT(*) SUM(X) ---------- ---------- 315 15165 select ( select SUM(X) from SOME_TABLE) as ONE, ( select SUM(X) from OTHER_TABLE) as TWO from dual; ONE TWO ---------- ---------- 6450 15165