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 
] )
for (Account acc : [
    SELECT Name, BillingAddress
    FROM Account
    WHERE BillingAddress IN :SameBillingAddress
    system.debug(+ acc.BillingAddress);

1 Answer 1


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
  • Thank You Adrian. I will test it out and mark it closed. Apr 23, 2019 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, 2019 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); } Apr 23, 2019 at 22:37

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .