Why can't I select Id in a GROUP BY query?

Every selected field in a grouped query must be either a grouping column or an aggregate. `Id` is unique per row, so grouping on it would produce one group per record.

Does this replace Salesforce duplicate rules for leads?

No. Duplicate rules and matching rules prevent new duplicates at save time with fuzzy matching. This query is exact-match, and it is for auditing the records already in the org.

How do I catch duplicates that differ only in case?

SOQL grouping is case-insensitive for text fields in most orgs, but leading/trailing whitespace still splits groups. Export and normalise in a spreadsheet when the match must be fuzzy.

SOQL Query to Find Duplicate Leads by Email

Find duplicate leads in Salesforce with a GROUP BY / HAVING SOQL query on Email, then pull the underlying record IDs.

Updated 2026-09-09

Duplicate detection in SOQL is a two-step job: GROUP BY ... HAVING COUNT(Id) > 1 to find the offending values, then a second query to pull the records behind them.

Leads are high-churn: they are created by campaigns, converted, and archived, so almost every lead query wants a date or status filter.

Step 1 - find the duplicated values

HAVING filters the grouped result; WHERE filters rows before grouping. You will usually want both - WHERE to scope the time window, HAVING to keep only the collisions.

Duplicated Email values

SELECT Email, COUNT(Id) total
FROM Lead
GROUP BY Email
HAVING COUNT(Id) > 1
ORDER BY COUNT(Id) DESC

Step 2 - pull the records behind a duplicate

Aggregate queries do not return record IDs. Take a value from step 1 and query the records so you can decide which one to keep.

The colliding records

SELECT Id, Name, Company, Status, LeadSource
FROM Lead
WHERE Email = 'paste-value-here'
ORDER BY CreatedDate

Scope it to recent data

On a large object, group across the whole table only once. After the initial cleanup, scope to a rolling window so the query stays fast and the list stays actionable.

Last 90 days only

SELECT Email, COUNT(Id) total
FROM Lead
WHERE CreatedDate = LAST_N_DAYS:90
GROUP BY Email
HAVING COUNT(Id) > 1

Common mistakes to avoid

Use the API name Lead, not the UI label - the two differ on several standard objects.
Wrap literal text values in single quotes; date literals such as LAST_N_DAYS:30 must stay unquoted.
Run the query in a sandbox first if it feeds a bulk update - a WHERE clause that is one character off can select the whole table.
GROUP BY cannot be combined with Id in the SELECT list - that is the error most people hit first.
Case and whitespace differences make near-duplicates invisible to GROUP BY; a trailing space is a different value.

Run it faster with TurboKit

Deduplication is an export-edit-reimport loop, and the export half is where most of the time goes.

TurboKit's AI SOQL builder lets you describe the result you want in plain English, returns the query, and exports the rows to CSV or Excel in the same panel - without leaving the Salesforce tab you are already on.

Frequently asked questions

Why can't I select Id in a GROUP BY query?: Every selected field in a grouped query must be either a grouping column or an aggregate. Id is unique per row, so grouping on it would produce one group per record.
Does this replace Salesforce duplicate rules for leads?: No. Duplicate rules and matching rules prevent new duplicates at save time with fuzzy matching. This query is exact-match, and it is for auditing the records already in the org.
How do I catch duplicates that differ only in case?: SOQL grouping is case-insensitive for text fields in most orgs, but leading/trailing whitespace still splits groups. Export and normalise in a spreadsheet when the match must be fuzzy.