0

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.

enter image description here

1

1 Answer 1

0

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
2
  • 1
    It worked!! Thank you so much!! Oh my goodness.. I spent hours yesterday trying to figure this out. I wanted to cry from the frustration! This is part of an assignment I was tasked with for a new job I'm interviewing for. Thank you so so much!
    – Liz
    Apr 12 at 23:12
  • Glad I could help! Let me know if the description needs some expanding.
    – Antyan
    Apr 13 at 13:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.