将多列SQL查询的[Union All]结果分组

威列旺卡

我有一个如下的交易表（存在更多列）。我一直在尝试在SQL查询中使用[Union]，但是当dealContributor1ACV中的值与dealContributor2ACV中的值相同时，总和似乎将其排除在外。（DCA）

dealContributor1	dealContributor2	dealContributor1ACV	dealContributor2ACV
直流	直流电	50000	50000
直流电	直流	20000	12000
直流	直流电	10000	17000
直流电	直流电	15000	11000

查找导致以下结构的查询。

dealContributor	dealContributorACV
直流	72000
直流电	11000
直流电	37000
直流电	65000

这是我现在正在使用的。

SELECT s.deal_contributer1 as 'dealContributor',s.deal_contributer1_acv as 'goalCompletionACV', GETDATE() as 'date2a'
FROM [dbo].[DynPSCsPersonGoals_20-21] g inner join [dbo].[HubspotDeal] s on g.email = s.deal_contributer1 WHERE s.Stage = 'closedwon' AND s.CloseDate >= '2020-09-01' AND s.CloseDate < '2021-01-01'
UNION
SELECT s.deal_contributer2 as 'dealContributor',s.deal_contributer2_acv as 'goalCompletionACV', GETDATE() as 'date2a'
FROM [dbo].[DynPSCsPersonGoals_20-21] g inner join [dbo].[HubspotDeal] s on g.email = s.deal_contributer2 WHERE s.Stage = 'closedwon' AND s.CloseDate >= '2020-09-01' AND s.CloseDate < '2021-01-01'
ORDER by dealContributor

提前致谢！

蒂姆·比格莱森（Tim Biegeleisen）

您需要合并然后聚合：

    SELECT contributor AS dealContributor, SUM(deal) AS dealContributorACV
FROM
(
    SELECT s.deal_contributer1 AS contributor, cast(s.deal_contributer1_acv as decimal) AS deal
    FROM [dbo].[DynPSCsPersonGoals_20-21] g
    INNER JOIN [dbo].[HubspotDeal] s ON g.email = s.deal_contributer1
    WHERE s.Stage = 'closedwon' AND
          s.CloseDate >= '2020-09-01' AND s.CloseDate < '2021-01-01'
    UNION ALL
    SELECT s.deal_contributer2, s.deal_contributer2_acv
    FROM [dbo].[DynPSCsPersonGoals_20-21] g
    INNER JOIN [dbo].[HubspotDeal] s ON g.email = s.deal_contributer2
    WHERE s.Stage = 'closedwon' AND
          s.CloseDate >= '2020-09-01' AND s.CloseDate < '2021-01-01'
) t
GROUP BY contributor;

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。