SOQL query not working with GROUP BY in semi-join

Question

Why is this SOQL query not working?

I'm trying to retrieve duplicate accounts, by I need the Id for each specific account that's why I didn't just use the subselect query which would give me a grouped result.

SELECT Id, ebMobile__ERPAccountNumber__c
FROM Account
WHERE Id IN (
    SELECT Id
    FROM Account
    WHERE ebMobile__ERPAccountNumber__c != null
    GROUP BY ebMobile__ERPAccountNumber__c
    HAVING COUNT(Id) > 1
)

Salesforce Inspector returns this error:

MALFORMED_QUERY:
GROUP BY ebMobile__ERPAccountNumber__c
^
ERROR at Row:7:Column:4
expecting a right parentheses, found 'GROUP'

Dev Console just says: Unknown error parsing query

Answer 1

Although not explicitly documented, the error is making it clear that you can't use GROUP BY in a semi-join. Likewise, you cannot use HAVING or COUNT() in a subquery (though these limitations are documented).

The documentation here is the Considerations for Semi-Join and Anti-Join queries section of the Comparison Operators page in the SOQL and SOSL reference.

I don't think there's a way for you to avoid using 2 separate queries here (one to get the duplicated ebMobile__ERPAccountNumber__c values, one to get the Account Ids that contain one of those values.

This will potentially be difficult to do if you're just working with the developer console. If I found myself with this task, I would:

• Use Dataloader to export all Account records that have a non-null value for your target field (you'd only need the Id and your target field)
• Open the resulting CSV in Excel
• Create a "pivot table" (containing both the Id and your target field)
• Add your target field and Id to the "rows" of the pivot table (in that order)
• Add your target field to the "values" of the pivot table (and make it count the number of occurrences)
• Click outside of the pivot table, then click "sort & filter" and select "filter" (this should give you the ability to filter on the "Count of [your field here]" column in the pivot table
• Filter out the rows of the pivot table where the count = 1