AggregateResult Count help

Question

Sorry to bother you on a Saturday, but i need some help This is a two part question, so please be patient .I have created a fieldset on the account object called “HistoryTracking”.

1) I would like to count how many times the fields within the fieldset “HistoryTracking” is not null 2) count how many times there is a field

So For example in my sandbox, i have created four accounts. The field website is populated three out of four accounts Desired results;

Field :Website Populated :3 Total count : 4 I have wrote the following below( which more than likely wrong as my first time writing an aggregate query) but as you can see i have hardcoded “Website” Into the query. I have approx 10 fields in the fieldset and i don’t want to manually hard code these into the query

AggregateResult[] ARs
= [SELECT count(Id) myCount, website
  FROM HistoryTracking
  GROUP BY website];

Looking forward to your response

Answer 1

I don't understand the two parts of your question but the code below hopefully covers at least part of what you are looking for.

Because count(fieldName) is available, some simple dynamic SOQL can be used. The countNonNullFields method in this code (move it to some other class to use it) does what I think you want:

@isTest
private class DynamicAggregationTest {
    @isTest(SeeAllData=true)
    static void test() {
        String[] fields = new String[] {'Name', 'Birthdate', 'Email', 'Phone'};
        system.debug('>>> counts=' + countNonNullFields('Contact', fields));
    }
    private static Map<String, Integer> countNonNullFields(String type, String[] fields) {
        String[] selects = new String[] {};
        for (String f : fields) {
            selects.add('count(' + f + ') ' + f);
        }
        Map<String, Integer> counts = new Map<String, Integer>();
        String soql = 'select ' + String.join(selects, ', ') + ' from ' + type;
        // One row only is returned
        for (AggregateResult ar : Database.query(soql)) {
            for (String f : fields) {
                counts.put(f, (Integer) ar.get(f));
            }
        }
        return counts;
    }
}

as it executes this dynamic SOQL query:

select count(Name) Name, count(Birthdate) Birthdate, count(Email) Email, count(Phone) Phone
from Contact

and returns a map of field names to non-null counts. The count for "Name" provides the total row count as "Name" is never null.

If you use this code, you should get rid of the SeeAllData=true in the test and instead insert test data and add asserts for the expected results.

PS

Doing some more testing of this I hit an "Internal Salesforce.com Error" a couple of times and also found that lookup fields are not supported. So proceed with caution.