Problem: Getting different results from SUM queries

When I run the below query:

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

I get these 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 looks like the result from the first query is incorrect. Can anybody point me to the right direction?

---

When doing this:

select * from table1 t1, table2 t2

you're actually cross joining both tables, resulting in a cartesian product (every row in t1 is combined with every row in t2).

You're probably missing a 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 a union of these two separate queries select 't1', sum(total_amount) from t1 union select 't2', sum(total_amount) from t2

This will show the sums in two rows instead of columns, but it's the easiest way AFAIK.

---

Hmmm, did you google for answers before writing the question? Google "+oracle +combining tables" gives a good answer rightaway: Oracle/SQL - Combining counts from 'unrelated' unrelated tables

Since you indicate that the table are unrelated, just with similar data, a UNION is probably the nicer solution. But along the lines of the prior question above you can select the partial results and then combine them with the ever willing the donate "select from dual" (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