+ Start a Discussion
MNRobMNRob 

Help with SOQL - Field must be grouped or aggregated

Reading through other examples and I still can't see my issue.

SELECT CSR__r.name, sum(margin__c) FROM shipment__C WHERE Actual_Delivery_Date__c = this_month  group by CSR__r.name.

If I drop the aggregate and group by my simple list comes back just fine.

Thanks in advance!
Terri T JilesTerri T Jiles
Hi MNRob,

Happy New Year!

Try Replacing the WHERE clause with the HAVING clause 

For example

SELECT CSR__r.name, sum(margin__c)
FROM shipment__C
GROUP BY CSR__r.name
HAVING Actual_Delivery_Date__c = this_month  

Please see this guide for more detail https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_having.htm
Alain CabonAlain Cabon
Hello,

The only constraint is to have a groupable field and Name could not be:

Verify if your field is groupable with the developer console and the anonymous windows (CTRL + E)
Schema.DescribeFieldResult dfr = CSR.name.getDescribe();
system.debug('field CSR name: ' + dfr.isgroupable());

If dfr.isgroupable() is false, you have used a field Name of type Auto Number probably.

The query below works fine and you have used this exact pattern for your query but the Name has a Text type for Account:
select account__r.name,sum(test_number__c)
from testall__c
where test_date__c = THIS_MONTH
group by account__r.name

Best regards

Alain

 
MNRobMNRob
Terri, I still got the error when using the having clause as suggested unfortunately. Alain, good thought. I ran the command as suggested in the anonymous window, but the field is groupable.

I appreciate both of you taking the time to respond!
Terri T JilesTerri T Jiles
What is the exact error message you are receiving?  Can you please include it?

I assume you already validated there is data stored for that object ;)
Alain CabonAlain Cabon
  1. Another option would be a governor limit (more than 50,000 rows) but the error should be different.
  2. From the workbench and the REST expolorer (Utillities): 

Replacing the banks with a sign "+" in the query.
 
/services/data/v37.0/query?q=SELECT+CSR__r.name,sum(margin__c)+FROM+shipment__C+WHERE+Actual_Delivery_Date__c=this_month+group+by+CSR__r.name

User-added image

"Show Raw Response". and just copy/paste the response here without the cookie.

Alain