I want to check if there are any duplicate AccountTeamMembers on Account.

Example : If any account has 2 AccountTeamMembers with TeamMemberRole = 'Account Executive'

Is this possible in SOQL ?

Tried following soql, but it does not give all records with count (Not a complete solution)

select count(id) from AccountTeamMember where TeamMemberRole='Account Executive' group by AccountId

Attribution to: Prafulla Patil

Possible Suggestion/Solution #1

Aggregate queries do not give you full result set by design. You should apply LIMIT clause for a start or query from Apex calling queryMore() / using for loop

However something like that should get you started:

SELECT COUNT(Id), AccountId
FROM AccountTeamMember
WHERE TeamMemberRole = 'Account Executive'
GROUP BY AccountId
HAVING COUNT(Id) > 1
ORDER BY COUNT(Id) DESC
LIMIT 1000

Attribution to: eyescream

Possible Suggestion/Solution #2

This will give you a list of the Roles that are duplicated. I would assume you would constrain the result by AccountId(s).

for (AggregateResult actRole : [select count(id) counts, TeamMemberRole from AccountTeamMember group by AccountId, TeamMemberRole])
    if(((integer)actRole.get('counts')) > 1)
    System.debug ('**************** Duplicate ' + actRole.get('TeamMemberRole'));

Attribution to: techtrekker
This content is remixed from stackoverflow or stackexchange. Please visit https://salesforce.stackexchange.com/questions/4319