0

I created a Custom Settings and add some records in it. On the basis of that value i write an aggregate query and i want to store that field value in String.

    // List to fetch all records from Custom settings
List<Region_and_Country_Mapping__c> csList = Region_and_Country_Mapping__c.getall().values();

// Created a map to store fields of custom link
Map<string, string> countryCodeVsCountryNameMap = new Map<string, string>();
for(Region_and_Country_Mapping__c cs : csList) {
        if(cs.Region_Name__c == regionSelected) {
             countryCodeVsCountryNameMap.put(cs.Country_Code__c, cs.Country_Name__c);
        }
 }

 AggregateResult[] resultsStateCount = [SELECT BillingCity, count(Id) from Account WHERE BillingCountryCode IN :countryCodeVsCountryNameMap.keySet() GROUP BY BillingCity];
 for(AggregateResult ar : resultsStateCount) { 
         ClientGeoLocationDetailWrapper clientGeoDetail = new ClientGeoLocationDetailWrapper();
          string cityName = String.valueOf(ar.get('BillingCity'));
          string cityCount = String.valueOf(ar.get('expr0'));

          // want to fetch country code
          string CountryCode = BillingCountryCode 

In Code i clearly mention all senarios please review and help me to fetch BillingCountryCode

  • You are not querrying BillingCountryCode in SOQL – Pranay Jaiswal Feb 6 at 10:36
  • Yes because if i query BillingCountryCode then i have to group by with BillingCountryCode which i don't want. – Hennry Feb 6 at 10:45
1

In order for you to get BillingCountryCode field, you will have to include that field in your SOQL query and you will have to group that field as well.

So you will have to change your SOQL query to below.

AggregateResult[] resultsStateCount = [SELECT BillingCity,BillingCountryCode,count(Id) countId from Account WHERE BillingCountryCode IN :countryCodeVsCountryNameMap.keySet() GROUP BY BillingCity, BillingCountryCode];  

And then you can get the value of BillingCountryCode as you do for other fields

string cityName = String.valueOf(ar.get('BillingCity'));
string cityCount = String.valueOf(ar.get('countId'));
// want to fetch country code
string CountryCode = String.valueOf(ar.get('BillingCountryCode')); 
  • i dont want to group by BillingCountryCode – Hennry Feb 6 at 10:47
  • Without grouping it, you won't be able to get the value of BillingCountryCode from AggregateResult. If you don't group by BillingCountryCode, the value of BillingCountryCode would be different for the records in a single group i.e. All the records with same BillingCity can have different BillingCountryCode, so in that case, what will be your logic to pick which value from that set of values ? – Vijay Ganji Feb 6 at 10:58

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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