How to do a SUM across two unrelated tables?

Question

I'm trying to sum on 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 column named sum_1 and sum_2. However, postgres doesn't give me any result for this query.

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;

The FULL JOIN is used with a dud condition so that either subquery could produce no results (empty) without causing the greater query to have no result.

I don't think the query as you have written would have produced the result you expected to get, because it's doing a CROSS JOIN between table1 and table2, which would inflate each SUM by the count of rows in the other table. Note that if either table1/table2 is empty, the CROSS JOIN will cause X rows by 0 rows to return an empty result.

Look at this SQL Fiddle and compare the results.