How to make SUM through two unrelated tables?

Question

I am trying to summarize two unrelated tables with postgres. With MySQL, I would do something like this:

SELECT SUM(table1.col1) AS sum_1, SUM(table2.col1) AS sum_2 FROM table1, table2 

This should give me a table with two columns named sum_1 and sum_2. However, postgres does not give me any result for this request.

Any ideas?

Answer 1

 SELECT (SELECT SUM(table1.col1) FROM table1) AS sum_1, (SELECT SUM(table2.col1) FROM table2) AS sum_2; 

You can also write it as:

 SELECT t1.sum_c1, t1.sum_c2, t2.sum_t2_c1 FROM ( SELECT SUM(col1) sum_c1, SUM(col2) sum_c2 FROM table1 ) t1 FULL OUTER JOIN ( SELECT SUM(col1) sum_t2_c1 FROM table2 ) t2 ON 1=1; 

FULL JOIN is used with the dud condition, so any subquery cannot give any results (empty), without causing a larger query to fail.

I don’t think that the query you wrote would give the result you expected to receive, because it makes CROSS JOIN between table1 and table2, which inflates each SUM by the number of rows in another table. Note that if table1 / table2 is empty, CROSS JOIN will cause X rows to 0 rows to return an empty result.

Take a look at SQL Fiddle and compare the results.

Answer 2

I suggest something like the following, although I have not tried it.

 select sum1, sum2 from (select sum(col1) sum1 from table1), (select sum(col1) sum2 from table2); 

The idea is to create two inline views, each with one row, and then make a Cartesian join on these two views, each of which has one row.

Answer 3

To combine multiple aggregates from multiple tables, use CROSS JOIN :

 SELECT sum_1, sum_2, sum_3, sum_4 FROM (SELECT sum(col1) AS sum_1, sum(col2) AS sum_2 FROM table1) t1 CROSS JOIN (SELECT sum(col3) AS sum_3, sum(col4) AS sum_4 FROM table2) t2 

There is always exactly one row from any of the subqueries, even without rows in the source tables. So the easiest way is CROSS JOIN (or even just a low comma between subqueries - it’s not so easy to read shorthand for cross-connection with a lower priority).

Note that this creates a cross join between individual rows in the aggregate, and not a cross join between separate rows from multiple tables, such as your wrong statement in question, - thus multiplying each other.