12

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.

3 Answers 3

13

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
2
  • 1
    This approach actually worked.
    – PepeFloyd
    Apr 19, 2013 at 14:28
  • This helped me out to Mike!
    – Daft
    Sep 4, 2014 at 10:36
4

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.

2
  • My T-SQL background has betrayed me; I wasn't aware of those restrictions on count().
    – Mike Chale
    Apr 19, 2013 at 14:22
  • We all come from different backgrounds Mike. I've still lots to learn! ;)
    – crmprogdev
    Apr 19, 2013 at 14:30
0

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

You must log in to answer this question.

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