1

My goal is to get the ID and the COUNT(ID) [ie.expr0]. ? But i am struggling here! Any other way

  SELECT  COUNT(Id),Id, Application_Prefix__c FROM Test__c GROUP BY Application_Prefix__c  //ERROR Id should be group by

i tried

SELECT COUNT(Id),Id, Application_Prefix__c FROM Test__c GROUP BY Application_Prefix__c,Id //ERROR aggregate can't be used in group by
  • You can't have count(Id) and have Id in the select close ! if you don't group by Id – benahm Apr 3 '17 at 12:51
  • 2
    You seem to be having an X-Y Problem here. You've selected an approach that is not possible within the constraints of the Salesforce platform. If you back up and explain what you are trying to achieve at a high level, you will get better advisement. Please edit your post to be more clear about what you are trying to achieve. – Adrian Larson Apr 3 '17 at 12:55
  • Agree. Updating the post. – bharath Apr 3 '17 at 13:05
6

If you want to count how many records match each Application_Prefix__c, then you should remove Id from your SELECT clause:

SELECT COUNT(Id), Application_Prefix__c FROM Test__c GROUP BY Application_Prefix__c

You can't really get both the individual Id values and the count per prefix in a single query. If you wanted to get the counts using post-processing, you could do a Map<String, List<Test__c>> or even just a Map<String, Integer>. Here is a common grouping pattern:

List<Test__c> allRecords = [SELECT Application_Prefix__c FROM Test__c];
Map<String, List<Test__c>> prefixes = new Map<Id, List<Test__c>();
for (Test__c record : allRecords)
{
    if (!prefixes.containsKey(record.Application_Prefix__c))
        prefixes.put(record.Application_Prefix__c, new List<Test__c>());
    prefixes.get(record.Application_Prefix__c).add(record);
}
| improve this answer | |
  • Yes. But i need to pull the ID from the aggregate query to process some other record which is depend on this id value. I dont want to go for another SOQL to pull the ID. – bharath Apr 3 '17 at 12:51
  • 1
    That doesn't make any sense at all. Aggregates by definition represent more than one record... – Adrian Larson Apr 3 '17 at 12:52
1

The issue appears to be with the order of your statements related to Id.

SELECT Id, COUNT(Id), Application_Prefix__c FROM Test__c GROUP BY Application_Prefix__c, Id 

Edit

Problem is caused by trying to use groupBy on a field that you're also aggregating. I think you may want to try using Group by Rollup or possibly Grouping if you don't want to manually calculate the count.

| improve this answer | |
  • Count(Id) and groub by Id in that case I think that Count(Id) will be equal to 1 – benahm Apr 3 '17 at 12:54
  • You'll only have one Count(Id) record, but you'll have all of the record Ids. Perhaps you want to groupby Application_Prefix__c ? – crmprogdev Apr 3 '17 at 12:54
  • Got error [object Object]: SELECT Id, COUNT(Id), Application_Prefix__c FROM Test__c GROUP BY Application_Prefix__c, Id ^ ERROR at Row:1:Column:18 Grouped field should not be aggregated: I – bharath Apr 3 '17 at 12:55
  • Not sure, aggregateResult object is a list and can contain multiple values, in this cas as much as there is Ids – benahm Apr 3 '17 at 12:56
  • 1
    Aggregate result is a list. What I suggest is that you remove the count(ID) and since you have all the Ids, count them manually or run the query again separately. Your error is because you're grouping on the field that you're also aggregating. Remove the groupby clause and you'd be fine with the above query. – crmprogdev Apr 3 '17 at 12:57

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.