function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
RarLopzRarLopz 

BillingAddress does not support aggregate operator COUNT

How Can i get a list of Accounts that has the same BillingAddress? 

I tried this and 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'

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);
} 
Best Answer chosen by RarLopz
Raj VakatiRaj Vakati
Compound fields is not supported in the aggregate result .. you need to count the separately ..  You need to aggregate each field individually. 


Like below  
Set <String>SameBillingAddress = new Set<String>();
for (AggregateResult aggregate : [Select count(Id),BillingStreet str ,BillingState stt 
                                  From Account 
                                  Where BillingStreet!= null AND BillingState!=null AND BillingCity!= null AND BillingPostalCode!=Null
                                  Group By BillingStreet,BillingState
                                  HAVING count(BillingStreet) > 1 
                                 ] )
{
    SameBillingAddress.add((String)aggregate.get('str'));
}

 

All Answers

Raj VakatiRaj Vakati
Compound fields is not supported in the aggregate result .. you need to count the separately ..  You need to aggregate each field individually. 


Like below  
Set <String>SameBillingAddress = new Set<String>();
for (AggregateResult aggregate : [Select count(Id),BillingStreet str ,BillingState stt 
                                  From Account 
                                  Where BillingStreet!= null AND BillingState!=null AND BillingCity!= null AND BillingPostalCode!=Null
                                  Group By BillingStreet,BillingState
                                  HAVING count(BillingStreet) > 1 
                                 ] )
{
    SameBillingAddress.add((String)aggregate.get('str'));
}

 
This was selected as the best answer
RarLopzRarLopz
Thanks @RAjVakati . Here's my final code. So far so good.
  
Set <String> SameBillingAddress = new Set<String>();
for (AggregateResult aggregate : [Select count(Id),BillingStreet str,BillingCity ct, BillingState stt, BillingPostalCode pc 
                                  From Account 
                                  Where BillingStreet!= null AND BillingState!=null AND BillingCity!= null AND BillingPostalCode!=Null
                                  Group By  BillingStreet,BillingCity,BillingState,BillingPostalCode
                                  HAVING count(BillingPostalCode) > 1 
                                 ] )
{
    
	 SameBillingAddress.add((String)aggregate.get('str')+(String)aggregate.get('ct')+(String)aggregate.get('stt')+(String)aggregate.get('pc'));
	
   
}
system.debug('Check ths size ' + SameBillingAddress.size());
system.debug('Check the contents ' + SameBillingAddress); 

DEBUG LOGS: 
User-added image

//Now,  comb through all Accounts, and check if the, //BillingStreet+BillingPostalCode exists in the set //SameBillingAddress,
// if it does then create a list of all these Accounts, //and print the results in Debug logs 
 
 
FOR(Account acc :[SELECT Name, RouteId__c,BillingStreet,BillingCity, BillingState, BillingPostalCode
    					FROM Account 
    					WHERE (BillingStreet IN :SameBillingAddress AND BillingPostalCode IN :SameBillingAddress )
                 ]){
                     
                     system.debug(+acc.Id + ' ' acc.Name + ' ' +acc.BillingAddress);
                 }

 
RarLopzRarLopz
Note: This script works great against small data set, but when i try to execute this script in Production I get the error 

System.QueryException: Aggregate query does not support queryMore(), use LIMIT to restrict the results to a single batch