Getting the sum of several columns from two tables
Solution 1
You can union your tables before the group by (this is on Oracle, by the way):
SELECT t.month_ref, SUM(t.amount1), SUM(t.amount2)
FROM (SELECT month_ref, amount1, amount2
FROM T_FOO
WHERE seller = XXX
UNION ALL
SELECT month_ref, amount1, amount2
FROM T_BAR
WHERE seller = XXX
) t
GROUP BY t.month_ref
You may also union the tables with the seller field and filter by it later (in case you need more advanced logic):
SELECT t.month_ref, SUM(t.amount1), SUM(t.amount2)
FROM (SELECT month_ref, amount1, amount2, seller
FROM T_FOO
UNION ALL
SELECT month_ref, amount1, amount2, seller
FROM T_BAR) t
where t.seller = XXX
GROUP BY t.month_ref
Solution 2
Have you tried using a union?
SELECT MONTH_REF, SUM(amount1), SUM(amount2)
FROM (
SELECT MONTH_REF, SUM(amount1) AS amount1, SUM(amount2) as amount2
FROM T_FOO
WHERE seller = XXX
GROUP BY MONTH_REF
UNION ALL SELECT MONTH_REF, SUM(amount1), SUM(amount2)
FROM T_BAR
WHERE seller = XXX
GROUP BY MONTH_REF
) tmp
GROUP BY MONTH_REF
Solution 3
Alternatively, an outer join should also work:
SELECT month_ref,
SUM(t_foo.amount1) + SUM(t_bar.amount1),
SUM(t_foo.amount2)+SUM(t_bar.amount2)
FROM t_foo FULL OUTER JOIN t_bar
ON t_foo.month_ref = t_bar.month_ref
GROUP BY month_ref
Solution 4
I finally get this working using the Lieven's answer.
Here is the correct code (amount1 = ...
is not working on my environment, and there are too many ;
in the query):
SELECT MONTH_REF, SUM(sumAmount1), SUM(sumAmount2)
FROM (
SELECT MONTH_REF, SUM(amount1) as sumAmount1, SUM(amount2) as sumAmount1
FROM T_FOO
WHERE seller = XXX
GROUP BY MONTH_REF
UNION ALL SELECT MONTH_REF, SUM(amount1), SUM(amount2)
FROM T_BAR
WHERE seller = XXX
GROUP BY MONTH_REF
) tmp
GROUP BY MONTH_REF
Romain Linsolas
As a software engineer and specialist in Java / J2EE application development, I am particularly interested in designing and developing high performance JEE applications, and Rich Internet Applications. My specialties: IT Consulting in Java / J2EE development (Spring, Hibernate, JSF, Struts) Web development (XHTML, Javascript, CSS, Ajax) Agile methodologies (Scrum, eXtreme Programming) Unit Testing and Quality, Continuous Integration and Performance
Updated on July 09, 2022Comments
-
Romain Linsolas almost 2 years
I want to get the sum of several columns from 2 different tables (these tables share the same structure).
If I only consider one table, I would write this kind of query:
SELECT MONTH_REF, SUM(amount1), SUM(amount2) FROM T_FOO WHERE seller = XXX GROUP BY MONTH_REF;
However, I would like to also work with the data from the table T_BAR, and then have a
select
query that return the following columns:- MONTH_REF
- SUM(T_FOO.amount1) + SUM(T_BAR.amount1)
- SUM(T_FOO.amount2) + SUM(T_BAR.amount2)
everything grouped by the value of
MONTH_REF
.Note that a record for a given
MONTH_REF
can be found in one table but not in the other table. In this case, I would like to get the sum ofT_FOO.amount1 + 0
(or0 + T_BAR.amount1
).How can I write my SQL query to get this information?
For information, my database is Oracle 10g.