i do not want certain fields to be in the group by

Question

so i know my query works below. but i would like to also query these two fields: version__c, status__c

I do not want these two fields in the group by (but its making me)

Is there any way i can have it in the select but not in the group by?

query with out the two fields:

select  Contact__r.Id, Contact__r.Name ,  Contact__rEmail, Certification_Type__c, MAX(Date_Received__c), MAX(Name)
from Certification__c",
where Contact__r = '{0}' AND  Contact__r.No_longer_there__c != true and Status__c = 'Active'
GROUP BY  Contact__r.Id, Contact__r.Name , Contact__r., Certification_Type__c

Answer 1

In this context, querying fields without applying an aggregate function or grouping doesn't make much sense.

Let's say you have the following records:
Certification__c(ContactId = 1, version__c = 1, status__c = 'open')
Certification__c(ContactId = 1, version__c = 2, status__c = 'closed')
Certification__c(ContactId = 1, version__c = 2, status__c = 'closed') Certification__c(ContactId = 1, version__c = 1, status__c = 'closed') Certification__c(ContactId = 2, version__c = 2, status__c = 'closed')

Your query will group both of these records together into an AggregateResult. If you don't use an aggregate function to choose the value of version__c from one of those two records, and don't group by the values, which field value would be returned?

What if you wanted the field value for a different record in the result?

There isn't a way for a machine to make a decision one way or the other, which is why Salesforce is giving you that error.

Grouping by multiple fields gives you multiple AggregateResult records. Something like

AggregateResult(ContactId = 1, version__c = 1, status = 'open')
AggregateResult(ContactId = 1, version__c = 1, status = 'closed')
AggregateResult(ContactId = 1, version__c = 2, status = 'closed')
AggregateResult(ContactId = 2, version__c = 1, status = 'closed')

which is based on the order that you specify fields in the GROUP BY clause.

If you don't want to do that, then you might want to consider a parent-child subquery on Contact with Certification__c as the sub-query in the SELECT clause.

This would give you a similar grouping, doing the heavy lifting of packaging the Certification__c records based on which Contact they belong to, while leaving you free to not group by other fields as well.

Of course, the drawback to that approach is that you can no longer use aggregate functions to, say, get the most recent date. You could overcome this particular example by additionally using ORDER BY Date_Received__c DESC, but any other aggregate function you'd apply would need to be replicated in Apex.