Getting the sum of several columns from two tables

Question

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 of T_FOO.amount1 + 0 (or 0 + T_BAR.amount1).

How can I write my SQL query to get this information?

For information, my database is Oracle 10g.

score 12 · Accepted Answer · answered Sep 07 '09 at 09:16

12

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

answered Sep 07 '09 at 09:16

Kobi

135,331
41
252
292

Finally I prefer your solution (the first one) as it is clearer than the Lieven solution... – Romain Linsolas Sep 07 '09 at 09:26
First one is more efficient because you are reducing the number of rows sooner than when you move the `WHERE` clause outside of the sub `SELECT`. – JohnB Feb 20 '12 at 05:54
Thanks i used second one. – Emmanuel Angelo.R Aug 26 '15 at 09:28

Lieven Keersmaekers · Answer 2 · 2009-09-07T09:09:32.590

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

edited Sep 07 '09 at 09:09

answered Sep 07 '09 at 08:40

Lieven Keersmaekers

57,207
13
112
146

@romaintaz: no problem. One question, have you profiled the different solutions? I assume that *grouping by - union - grouping by* will be faster on large datasets than just *union - grouping by* solution. – Lieven Keersmaekers Sep 07 '09 at 11:13

score 1 · Answer 3 · answered Sep 07 '09 at 08:47

1

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

answered Sep 07 '09 at 08:47

Ruffles

79
1
3

This query takes too much time (especially compared to the Lieven's answer), and in addition it returns wrong results. It also need NVL(SUM(...), 0) otherwise I will get null values... – Romain Linsolas Sep 07 '09 at 09:09

score 1 · Answer 4 · edited May 23 '17 at 11:52

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

score -1 · Answer 5 · edited Nov 25 '15 at 16:19

-1

SELECT (SELECT SUM(amount) from debit) as debitamounts, (SELECT SUM(amount) from credit) as creditamounts

edited Nov 25 '15 at 16:19

Tunaki

132,869
46
340
423

answered Nov 25 '15 at 16:17

sagar roy

1

Please add some comments about your solution on why and how it solves the problem – Bhavesh Odedra Nov 25 '15 at 17:12

Getting the sum of several columns from two tables

5 Answers5

Linked