Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

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

share|improve this question

3 Answers 3

up vote 2 down vote accepted

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.

share|improve this answer
    
Is this correct syntax for child=Feedback_Comment and parent=Feedback ? –  MnZ May 13 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 at 13:55
    
You code is working. Also thanks for the suggestion. I'm accepting your answer :) –  MnZ May 13 at 14:14

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

share|improve this answer
    
Yes @atul this seems to be most probable way. How do I merge the results? Can you share some help files. –  MnZ May 13 at 14:01

Would this work?

SELECT Feedback.Name, Count(Id)
FROM Feedback_Comments__c
GROUP BY Feedback.Name
share|improve this answer
    
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 at 13:45
    
Try "SELECT Feedback.Name, Count(Id) FROM Feedback_Comment__c GROUP BY Feedback.Name" - removed potential typo with extra s. –  Richard Durrant May 13 at 14:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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