Error: Field must be grouped or aggregated

Question

We have a lookup relationship between a custom object Problem__c and cases, one problem can be related to many cases. I am trying to query the field or problem records and the number of related cases, this is working partially by doing this.

select problem__r.name,count(casenumber) from case where problem__c!=null group by problem__r.name limit 10

The problem is that I need to get more fields from the problem object so I tried this

select problem__r.name,problem__r.status__c,count(casenumber) from case where problem__c!=null group by problem__r.name limit 10

But I get the error Error: Field must be grouped or aggregated Status__c. How can I retrieve more fields from the parent object using this query?

Thanks.

Answer 1

When using aggregate functions like count, all other fields must be grouped. Try

select problem__r.name,problem__r.status__c,count(casenumber) 
from case where problem__c!=null 
group by problem__r.name, problem__r.status 
limit 10

Answer 2

The real problem with your query is that you added Count(fieldname) to it. Per the SOQL documentation:

Note the following when using COUNT():

■COUNT() must be the only element in the SELECT list.

■You can use COUNT() with a LIMIT clause.

■You can't use COUNT() with an ORDER BY clause. Use COUNT(fieldName) instead.

■You can't use COUNT() with a GROUP BY clause for API version 19.0 and later. Use COUNT(fieldName) instead.

Had you simply added the problem__r.status__c and casenumber fields you'd have been fine with adding those fields. If you wanted to get subtotals, you might want to consider using GROUP By ROLLUP. That would have allowed you to do something like this:

select problem__r.name,problem__r.status__c,count(casenumber) cnt from case where problem__c!=null GROUP BY ROLLUP (problem__r.name)  limit 10

BTW, COUNT_DISTINCT(fieldname) only returns non-null values. It might be worth playing with to see if using it would possibly help you change the syntax of your query to get the same results.

Answer 3

Its worth noting that the filter fields as well as the select fields need to be grouped by.

Works:

SELECT Vendor__c, COUNT(Id)vcaCount
FROM FFVendorCodeAssignment__c
GROUP BY Vendor__c, Status__c, Deleted__c
HAVING Status__c = 'Approved'
AND Deleted__c = FALSE

Fails:

SELECT Vendor__c, COUNT(Id)vcaCount
FROM FFVendorCodeAssignment__c
GROUP BY Vendor__c
HAVING Status__c = 'Approved'
AND Deleted__c = FALSE