4

I have a parent object Feedback that has multiple Feedback_Comments as child(lookup). I have counted total Feedbacks grouping by name. But I'm unable to count the childs grouping by name. Here's what I tried...

public with sharing class countAll {

public Summary[] Summaries { get; set; }

public countAll () {
    AggregateResult[] results = [
        SELECT Name, Count(id) Quantity,(SELECT Count(Id) FROM Feedback_Comments__r) FROM Feedback__c GROUP BY Name
    ];
    Summaries = new List<Summary>();
    for (AggregateResult ar : results) {
        Summaries.add(new Summary(ar));
    }
}

// wrapper class to hold aggregate data
public class Summary {
    public Integer Quantity { get; private set; }
    public String Name { get; private set; }

    public Summary(AggregateResult ar) {
        Quantity = (Integer) ar.get('Quantity');
        Name = (String) ar.get('Name');
    }
 }

}

I get this ERROR

Error: Compile Error: only root queries support aggregate expressions at line 6 column 37   

Please suggest me a way. Thanks

3 Answers 3

4

Try:

Select Feedback__r.Name, Count(Id)
From Feedback_Comment__c 
Group By Feedback__r.Name

Alternatively, if the Feedback__c and the Feedback_Comment__c records have a master-detail relationship, you could create a Roll Up Summary Field in the Feedback__c object of type COUNT that will count all of the children for a particular Feedback.

You could then use that in a query on the Feedback__c record e.g.

Select Name, Num_Children_Roll_Up__c 
From Feedback__c

This will get you the same data. It's a little difficult to know exactly what you want from your post. I'm assuming that you don't actually need to count the parent records as you have tried to do in your original query because it seems that you are grouping by name which I'm also assuming will be fairly unique values. So I think you only did that as a way to group the child records.

2
  • Is this correct syntax for child=Feedback_Comment and parent=Feedback ?
    – MnZ
    May 13, 2014 at 13:52
  • This will count all of the Feedback_Comment__c records and then group those counts by the name of the parent Feedback__c record. So if 5 of the Feedback_Comment__c records have a parent Feedback__c record with the Name of 'Foo', and 8 of the Feedback_Comment__c records have a parent with the name of 'Bar', you will get 2 AggregateResult lines returned. One with a count of 5 and the other with a count of 8.
    – Joe
    May 13, 2014 at 13:55
2

You can not use aggregate expressions in sub query.

You can split your query in tow queries and then merge the result.

AggregateResult[] results = [SELECT Name, Count(id) Quantity FROM Feedback__c GROUP BY Name];
AggregateResult[] res = [SELECT Feedback__c, count(id) qty FROM Feedback_Comments__r GROUP BY Feedback__c];

And then merge the result

0
0

Would this work?

SELECT Feedback.Name, Count(Id)
FROM Feedback_Comments__c
GROUP BY Feedback.Name
2
  • If I try SELECT Feedback__c.Name, Count(Id) FROM Feedback_Comments__c GROUP BY Feedback.Name I get Error: Compile Error: sObject type 'Feedback_Comments__c' is not supported. If you are attempting to use a custom object, be sure to append the '__c' after the entity name.
    – MnZ
    May 13, 2014 at 13:45
  • Try "SELECT Feedback.Name, Count(Id) FROM Feedback_Comment__c GROUP BY Feedback.Name" - removed potential typo with extra s. May 13, 2014 at 14:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.