如何组合两个表并在列上应用sum-function

Question

亲爱的Stackoverflow社区，

我生成了两个新表来根据月份汇总数据，这两个表工作得非常好。这两个表有相同的结构(见截图)。

SELECT  Cluster,
format(Posteingang,"mm.yyyy") AS Monat,
COUNT(Posteingang_Aufwand) AS Posteingang_Anz,
ROUND((SUM(Posteingang_Aufwand))/60, 2) AS Posteingang_Std
INTO DOKU_POSTEINGANG_MONAT
FROM DOKU_AUFWAND INNER JOIN DOKU_VORGANG_BGD 
ON DOKU_AUFWAND.Vorgang_BGD = DOKU_VORGANG_BGD.Vorgang_BGD
WHERE Posteingang BETWEEN #01/01/2020# AND #12/31/2020#
GROUP BY format(Posteingang,"mm.yyyy"), Cluster 
SELECT Cluster,
format(Posteingang_Bestaetigung,"mm.yyyy") AS Monat,
COUNT(Posteingang_Aufwand_Bestaetigung) AS Posteingang_Bestätigung_Anz,
ROUND((SUM(Posteingang_Aufwand_Bestaetigung))/60, 2) AS Posteingang__Bestätigung_Std
INTO DOKU_POSTEINGANG_BESTÄTIGUNG_MONAT
FROM DOKU_AUFWAND INNER JOIN DOKU_VORGANG_BGD 
ON DOKU_AUFWAND.Vorgang_BGD = DOKU_VORGANG_BGD.Vorgang_BGD
WHERE Posteingang_Bestaetigung BETWEEN #01/01/2020# AND #12/31/2020#
GROUP BY format(Posteingang_Bestaetigung,"mm.yyyy"), Cluster

一个表的输出

在之后，我希望将这两个表的输出放在另一个汇总的中(xxx_Anz xxx_Std)。数据需要按四个"集群"进行分组。一次。下面的语句给出了某物的回报，然而，总结出来的数量实在太高了。

SELECT  a.Cluster,
(SUM(a.Posteingang_Anz) + SUM(b.Posteingang_Bestätigung_Anz)) AS Total_Anz,
(SUM(a.Posteingang_Std) + SUM(b.Posteingang__Bestätigung_Std)) AS Total_Std
FROM DOKU_POSTEINGANG_MONAT AS a INNER JOIN DOKU_POSTEINGANG_BESTÄTIGUNG_MONAT AS b ON a.Cluster = b.Cluster
GROUP BY a.Cluster

返回相同的结果。

SELECT  a.Cluster,
(SUM(a.Posteingang_Anz) + SUM(b.Posteingang_Bestätigung_Anz)) AS Total_Anz,
(SUM(a.Posteingang_Std) + SUM(b.Posteingang__Bestätigung_Std)) AS Total_Std
FROM (SELECT * FROM DOKU_POSTEINGANG_MONAT AS a 
INNER JOIN DOKU_POSTEINGANG_BESTÄTIGUNG_MONAT AS b 
ON a.Cluster = b.Cluster)
GROUP BY a.Cluster

你知道是什么问题吗?

提前感谢。

新浪干杯

Answer 1

我意识到了这个问题。我使用了UNION ALL函数，它可以工作。非常感谢你对我问题的回答。

Select  Cluster, 
SUM(Anzahl) AS Anzahl_Total,
SUM(Stunden) AS Stunden_Total
INTO AUFWAND_TOTAL
FROM  (SELECT a.Cluster, a.Anzahl, a.Stunden
FROM DOKU_POSTEINGANG_MONAT AS a
UNION ALL SELECT b.Cluster, b.Anzahl, b.Stunden
FROM DOKU_POSTEINGANG_BESTÄTIGUNG_MONAT AS b) 
GROUP BY Cluster