SELECT
ExternalId__c,
CALENDAR_YEAR(CustomDateField__c) year,
SUM(CustomAmountField__c) amount,
COUNT(Id) counter
FROM CustomObject__c
GROUP BY ROLLUP(
CALENDAR_YEAR(CustomDateField__c),
ExternalId__c
)
ORDER BY
ExternalId__c ASC NULLS FIRST,
CALENDAR_YEAR(CustomDateField__c) ASC NULLS LAST
LIMIT 2000
The current grammar only allows field names in a group by clause.
Originated in pmd/pmd#6478
According to https://developer.salesforce.com/docs/atlas.en-us.260.0.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select.htm there should be aggregate functions allowed in group by clauses.
Whether
CALENDAR_YEARis a valid function in that context, I don't know. The documentation only mentions some functions:However, in https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_date_functions.htm suddenly
CALENDAR_FUNCTIONis used in aGROUP BYclause.So, I assume, that the following SOQL query is indeed valid syntax:
The current grammar only allows field names in a group by clause.