Why am I getting an error when trying to get a unique list from contacts using group by in soql?
I am trying to get a unique list from contacts using a group by but for some reason I am getting an error. If I use Workbench with the same SOQL query it would be fine but I am getting a Syntax Error in Apex.
ListlstContact = ([SELECT ID, Name, Email FROM Contact WHERE Email IN: pAddress group by ID, Name, Email]); Error: Illegal assignment from Listto List
To resolve the error when trying to get a unique list from contacts using group by in soql, you must understand that -
The return type of any query using grouping and aggregate functions is List.You can iterate over the AggregateResult objects (after changing the type of your variable lstContact) to get the data you're querying, or you can approach the issue in a different way. It's not clear from your included code why you need to group at all, since you're not using any aggregate functions. You can query with an IN clause and even do ORDER BY Email if you'd like to sort your Contacts in that way.
If what you want is a count of Contacts with a given email address, with one unique row per email, you could do
List lstContact = [SELECT count(Id) recordCount, Email FROM Contact WHERE Email IN Address GROUP BY Email];
for (AggregateResult ar : listContact) {
System.debug('Count of email ' + ar.get('Email') + ' is ' + ar.get('recordCount'));
}