Grouping [Union All] result from multiple columns SQL query

user_15075349 0
Viewed 51

I have a deal-table as below (more columns exists). I have been trying to use [Union] in my SQL query but when the value in dealContributor1ACV is the same as in dealContributor2ACV, the sum seems to exclude it. (dcA)

dealContributor1 dealContributor2 dealContributor1ACV dealContributor2ACV
dcA dcD 50000 50000
dcC dcA 20000 12000
dcA dcC 10000 17000
dcD dcB 15000 11000

Looking for a query resulting in the below structure.

dealContributor dealContributorACV
dcA 72000
dcB 11000
dcC 37000
dcD 65000

Here is what I am using now.

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

Thanks in advance!

union group-by sql-server
1 Answers

You need to union then aggregate:

    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;
user_18632290
Related