0

I am trying to get a list of Accounts that has the same BillingAddress

I tried to get an aggregateResult however got the error:

Line: 2, Column: 34 HAVING count(BillingAddress)>1 ^ ERROR at Row:5:Column:49 field BillingAddress does not support aggregate operator COUNT'

How can I change my query to get the Accounts with same BillingAddress?

Set <String>SameBillingAddress = new Set<String>();
for (AggregateResult aggregate : [Select count(Id),BillingAddress
                                  From Account 
                                  Where BillingStreet!= null AND 
                                  BillingState!=null AND BillingCity!= null 
                                  AND BillingPostalCode!=Null
                                  Group By BillingAddress
                                  HAVING count(BillingAddress) > 1 
] )
{
    SameBillingAddress.add((String)aggregate.get('BillingAddress'));
}
for (Account acc : [
    SELECT Name, BillingAddress
    FROM Account
    WHERE BillingAddress IN :SameBillingAddress
 ]){
    system.debug(+ acc.BillingAddress);
} 
5

You need to aggregate each field individually. Also use count(Id) in your HAVING clause, just like you have it in your SELECT clause.

SELECT COUNT(Id), BillingStreet, BillingCity, BillingState, BillingPostalCode
FROM Account 
WHERE BillingStreet != null
AND BillingCity != null
AND BillingState != null
AND BillingPostalCode != null
GROUP BY BillingStreet, BillingCity, BillingState, BillingPostalCode
HAVING COUNT(Id) > 1 
  • Thank You Adrian. I will test it out and mark it closed. – user4947 Apr 23 '19 at 22:08
  • You might want to consider changing your query for example to filter for specific states at a time. Instead of BillingState != null, you might use BillingState = 'Alabama', then another where you do BillingState = 'Alaska'. Alternatively, you might just filter BillingState LIKE 'A%', and so on. – Adrian Larson Apr 23 '19 at 22:29
  • Thanks @AdrianLarson Should I modify my code as follows Set <ID>SameBillingAddress = new Set<ID>(); SameBillingAddress.add((String)aggregate.get('ID')); for (Account acc : [ SELECT Name, BillingStreet, BillingCity,BillingPostalCode FROM Account WHERE BillingAddress IN :SameBillingAddress ]){ system.debug(+ acc.BillingStreet + acc.BillingPostalCode); } – user4947 Apr 23 '19 at 22:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.