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?
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.
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 either of the subqueries, even with no rows in the source tables. So a CROSS JOIN
(or even just a lowly comma between the subqueries - being the not so easy to read shorthand for a cross join with lower precedence) is the simplest way.
Note that this produces a cross join between single aggregated rows, not a cross join between individual rows of multiple tables like your incorrect statement in the question would - thereby multiplying each other.
I suggest something like the following, although I hjaven't 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 it, and then do a cartesian join on these two views, each with one row.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With