+ Start a Discussion
WorfWorf 

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, OpportunityIDCOUNT(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?

 

JoyJobingJoyJobing

looks like you'll need to remove the first COUNT(OpportunityID)?

WorfWorf

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.

JoyJobingJoyJobing
Sorry, I can't seem to figure it out. Hopefully someone else will be able to help. If nothing else, you could export them all, then do your work in excel. Might be hugely painful, so maybe pull them out by Close Date year or something like that.
Raja IslamRaja Islam
Remove these fields from query  ID, ContactID, OpportunityID and it should work.
johannjohann
Here's a better version of the query:
select COUNT(Id), OpportunityID, ContactId from OpportunityContactRole 
where Role != NULL
GROUP BY OpportunityID, ContactId
HAVING COUNT(Id) > 1 limit 1500