Error: Field must be grouped or aggregated
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
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.