0

I am searching for the equivalent of a SOQL DISTINCT to use within my AggregateResult query below.

I would like to group my results using a field called "Servicer_Code__c". Would any one know if this is even possible?

Thanks in advance!

    private List<CoverageTeamMember> getCoverageTeam( Account account ) {
    List<CoverageTeamMember> result = this.getStaticCoverageTeam( account );

    for ( AggregateResult records : [ 
        SELECT Id, Servicer_Department_Name__c role, Servicer__r.Id name,    
    Servicer__r.Name actualname, Servicer__r.Email__c email, 
    Servicer__r.Work_Phone__c phone, Active__c a, Servicer_Code__c sc
        FROM Policy__c
        WHERE Account__c = :account.Id AND 
            Servicer_Department_Name__c != null AND
            Servicer__c != null AND
            Active__c = True
        GROUP BY Id, Servicer_Department_Name__c, Servicer__r.Id, Servicer__r.Name, Servicer__r.Email__c, Servicer__r.Work_Phone__c, Active__c, Servicer_Code__c
    ] ) {
        result.add( new CoverageTeamMember( records ) );
    }

2 Answers 2

1

Yes, that cannot be done. You can "group" by Servicer_Code__c, but that requires you to use an aggregate statement, as Adrian pointed out. You really do not want to SUM or AVG your PolicyIds :-)

There is some capability to nest query results in SOQL, but only if you are using two objects with a formal relationship. See the Left Outer Join example here SOQL relationships

Left Outer Join

Problem: Find all positions with their related list of applications.

Again, we would like to list the positions regardless of whether there is a related application or not.

SOQL Query:

SELECT Name, (SELECT Name FROM Job_Applications__r) FROM Position__c

2

If you want unique values for that one field, you should GROUP BY only that field:

SELECT ... FROM Policy__c WHERE ...
GROUP BY Servicer_Code__c

You can also build a collection of unique values after the fact using a Set:

Set<String> codes = new Set<String>();
for (AggregateResult aggregate : [/* query from OP */])
{
    //existing logic
    codes.add((String)aggregate.get('sc'));
}
3
  • not sure if this is a by-product or limitation of using AggregateResult but I receive this error > "Field must be grouped or aggregated: Id"... Thanks!
    – SFDC-ThEq
    Commented May 13, 2016 at 18:50
  • @SFDCCrystalCo You can only get data that is grouped or aggregated, just like it says. If a field is in your SELECT clause, it must be in your GROUP BY clause or wrapped in an operation (MAX, MIN, etc). You have to think hard about what data you actually need. It's hard to say what to strip out without more context.
    – Adrian Larson
    Commented May 13, 2016 at 18:52
  • The end product is list of records displayed within a Visualforce page component - you actually helped me out with this! My query is (correctly) returning a list unique Policy ID's but rather I want to group the results by the Servicer_Code__c field instead. It's looking like this is cannot be easily done within the boundaries of a SOQL query!
    – SFDC-ThEq
    Commented May 13, 2016 at 19:14

You must log in to answer this question.

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