You need to sign in to do that
Don't have an account?
SOQL Query for Duplicate Contact Roles in an opportunity.
Im relatively new to Database queries, but the situation is for a little while we had an online scripts creating creating duplicate entries for the contact role in each opportunity created. That problem was fixed but now I just need to create a SOQL report of just the duplicates so I can go through and delete one of each.
This is the SOQL Query I try:
select ID, ContactID, OpportunityID, COUNT(OpportunityID)
from OpportunityContactRole
where Role != NULL
GROUP BY OpportunityID
HAVING COUNT(OpportunityID) > 1
This is returning the error:
MALFORMED_QUERY:
ContactID, OpportunityID, COUNT(OpportunityID)
^
ERROR at Row:1:Column:40
Grouped field should not be aggregated: OpportunityId
Can someone help me with this?
looks like you'll need to remove the first COUNT(OpportunityID)?
Tried that, now it is telling me "Field must be grouped or aggregated: ContactID"
Then if I change the GROUP BY to ContactID, it tells me that the OpportunityID needs to be grouped or aggregated..... wait... didnt it already tell me It COULDNT be grouped or aggregated? thouroughly confused.
select COUNT(Id), OpportunityID, ContactId from OpportunityContactRole
where Role != NULL
GROUP BY OpportunityID, ContactId
HAVING COUNT(Id) > 1 limit 1500