Description: EXTRACT(YEAR From Date Time ) produces an error in a SQL GUI when grouping exists in a database on EXTRACT(YEAR FROM below SELECT "Car", SUM("Cost") , EXTRACT(YEAR FROM "ServiceDate") FROM "tblService" GROUP BY "Car", EXTRACT(YEAR FROM "ServiceDate") ORDER BY EXTRACT(YEAR FROM "ServiceDate") DESC Actual Results: SQL Status: HY000 Error code: 1000 syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE Expected Results: Car SUM Year 2015 121 2020 2015 546 2019 2015 399 2018 2015 187 2017 2015 98 2016 Reproducible: Always User Profile Reset: No Additional Info: It should have grouped on EXTRACT(YEAR From Date Time )
I'm wrong or you can't use order clause, sort order is fixed GROUP BY in the order of the fields. https://firebirdsql.org/manual/nullguide-aggrfunc.html#nullguide-aggrfunc-groupby
SQLstill errors even when Order by is removed.
Can't see anybody, who has confirmed a buggy behavior here. Have tested this: SELECT "Car", SUM("Cost") , EXTRACT(YEAR FROM "ServiceDate") AS "Year" FROM "tblService" GROUP BY "Car", "Year" ORDER BY "Year" DESC No problem to execute this query. But you can't change from this query to the query-GUI. It won't understand the code. Then I tried SELECT "Car", SUM("Cost") , EXTRACT(YEAR FROM "ServiceDate") FROM "tblService" GROUP BY "Car", EXTRACT(YEAR FROM "ServiceDate") ORDER BY EXTRACT(YEAR FROM "ServiceDate") DESC Without any alias and it throws an error, because Base couldn't understand this code. So I switched to direct SQL executing and this code works, too. Tested with LO 6.4.2.2 on OpenSUSE 15.1 64bit rpm Linux So I couldn't find any buggy behavior here. I switch it back to unconfirmed, because it must be confirmed by other person like the bug reporter.
I guess it was my fault for omitting the alias. Thanks for answer.