Skip to content

Syntax error when using function in GROUP BY in SOQL queries #84

@adangel

Description

@adangel

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_YEAR is 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_FUNCTION is used in a GROUP BY clause.

So, I assume, that the following SOQL query is indeed valid syntax:

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.

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type
    No fields configured for issues without a type.

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions