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.