Mysterious GROUP BY

[nofue]

In my database, I have a simple table tEffort

The query showing it’s rows works as expected:

But when I want the result to be grouped by the assignmentID, the fun ends:

I of course tried this with a much more complex query at first, but even when I watered the complexity down to

SELECT "effortDateTime", "effortID", "assignmentRef"

FROM "tEffort"

GROUP BY "assignmentRef"

ORDER BY "effortDateTime" DESC

I still get this error.

Question: What am I missing here?

Thanks for your hints in advance.

---

Version: 24.2.5.2 (X86_64) / LibreOffice Community
Build ID: bffef4ea93e59bebbeaf7f431bb02b1a39ee8a59
CPU threads: 8; OS: macOS 11.7.10; UI render: Skia/Raster; VCL: osx
Locale: de-AT (de_AT.UTF-8); UI: en-US
Calc: threaded

[nofue]

OK, got it. HSQLDB wants you to also list ALL the columns in the SELECT statement in the GROUP BY, in the order you want the grouping to be done. This works:

SELECT "effortDateTime", "effortID", "assignmentRef"

FROM "tEffort"

GROUP BY "assignmentRef",  "effortDateTime", "effortID"

ORDER BY "effortDateTime" DESC

Hope this helps …

[mariosv]

Grouping in Libreoffice Base query editor

[RobertG]

It is not typical HSQLDB. What data should be presented if you only group one column and there are different values for the other column. Might be you will show the sum, average or something like this for the other column.
If you use any function/group by every field needs a function/group by.

[sokol92]

@nofue

HSQLDB wants you to also list ALL the columns in the SELECT statement in the GROUP BY, in the order you want the grouping to be done

Documentation:

When a <group by clause> is used, only the columns used in the <group by clause> or expressions used there, can be used in the <select list>, together with any <aggregate function> on other columns.

[nofue]

Thank you all, late birds, for your wisdom. Has anybody ever noted the blunder the GUI makes in Query Design View? If it is necessary to have all the fields in the SELECT also in the GROUP, why, oh why doesn’t get the “GROUP BY” propagated to all fields in the SELECT when creating the SQL?

[sokol92]

@nofue

late birds

Time zone.

[Villeroy]

Writing SQL manually is easier than the struggling with the query designer. And writing SQL gives you plenty of options uncovered by that tool, for instance: relations other than TBL1.X=TBL2.Y, table aliases, nested functions, nested SELECT statements, switching to “direct SQL mode”.

When a query involves many columns, I collect all the column names in the query designer in order to avoid typos, switch to SQL view, copy everything to some code editor and later back to SQL view in order to test the code.

The parts of the SQL covering the creation of tables and indices is so easy, that I even type my table definitions directly into the SQL window. Way faster than clicking and some additional options too.

[nofue]

Yup, that’s exactly the way I got into this issue The query is much more complex than my example, and to stay safe from typos, I clicked my way through the designer. My mistake there was not to run the query – I just copied the stuff over and scratched hair für a while. But hey, I guess that’s what they call a “learning curve”

[fpy]

@nofue

What am I missing here?

the very concept of group by ? SQL GROUP BY Statement

write down an example with what you expect effortDateTime and effortID columns to show …