How do I get the sum of a field and avoid duplication from an inner join?

All we need is an easy explanation of the problem, so here it is.

I am pulling Geo from the Accounts table and I am ranking the Geos by Opportunity \$\$ Amt based on Accounts that have the name like "Brinks%".

Accounts are unique, and there can be many opportunities for every account. I need to add the number of employees for each Geo from the accounts table, but since there are many opportunities for each Geo, the number I’m getting is wrong. It seems to be getting multiplied by the number of opportunities it’s tied to. Do I need to change my join? Do I add a subquery? I tried, but I don’t think I’m doing it right. This is what I wrote:

``````Select

Ac.frm_GEO__c, sum(Op.Amount) Amt
,Rank () Over (Order By sum(Op.Amount) Desc) as 'Rank'
,sum(ac.NumberOfEmployees) num_empl

FROM Account Ac
Inner Join Opportunity Op on Op.AccountId=Ac.Id

Where Ac.Name like  'Brinks%'

Group by Ac.frm_GEO__c
``````

My results are shown below. It’s all correct except the last column.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I would solve this with a sub-query. I think that would be the best route available to you. As soon as you JOIN you have expanded the results to be at the granularity of the opportunity. It would be easier to never expand to this granularity and instead retrieve the counts from a subquery:

``````Select

Ac.frm_GEO__c, [SummarizedOpportunity].Amt
,Rank () Over (Order By [SummarizedOpportunity].Amt Desc) as 'Rank'
,sum(ac.NumberOfEmployees) num_empl

FROM Account Ac
JOIN (SELECT [Opportunity].[AccountId], [Amt] = SUM([Opportunity].[Amount]) FROM [Opportunity] GROUP BY [Opportunity].[AccountId]) [SummarizedOpportunity]
ON [SummarizedOpportunity].[AccountId] = Ac.Id

Where Ac.Name like  'Brinks%'

Group by Ac.frm_GEO__c
``````

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂