获得数列的和两个表(Getting the sum of several columns from

我想几列的2代不同表的总和（这些表共享相同的结构）。

如果我只考虑一个表，我会写这样的查询：

SELECT MONTH_REF, SUM(amount1), SUM(amount2)
    FROM T_FOO
    WHERE seller = XXX
    GROUP BY MONTH_REF;

不过，我想也有从表T_BAR数据的工作，然后有一个select返回下面的列的查询：

• MONTH_REF
• SUM（T_FOO.amount1）+ SUM（T_BAR.amount1）
• SUM（T_FOO.amount2）+ SUM（T_BAR.amount2）

一切由价值分组MONTH_REF 。

请注意，对于一个给定的记录MONTH_REF可以在一个表中，但不会在其他表中找到。 在这种情况下，我想获得的总和T_FOO.amount1 + 0 （或0 + T_BAR.amount1 ）。

我怎么能写我的SQL查询来获取这些信息？

有关信息，我的数据库是Oracle 10g中。

Answer 1:

您可以联合小组通过（这是甲骨文的方式）之前，你的表：

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

您也可以联合与后来卖家领域和过滤通过它（你需要更先进的逻辑的情况下）的表：

 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

Answer 2:

您是否使用工会试过吗？

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

Answer 3:

另外，外部联接也应努力：

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

Answer 4:

我终于用这个工作利芬的答案。

下面是正确的代码（ amount1 = ...不工作我的环境，有太多;查询）：

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

Answer 5:

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

文章来源: Getting the sum of several columns from two tables