Query sql error: Not in aggregate function or group by clause: org.hsqldb.Expression@1186c88

English
#1

Hi, I’m a very small business 1-man shop. I do my own development for myself (I’m a musician, not a developer)…I’m a bit of a noobie to SQL although I mostly have fun with database design, when I have the time. I tend to like the GUI vs SQL statements written up.

I’m stumped with this one. In my database I’m trying to build a query for a report utilizing 3 tables…
“accounts_receivable”
“clients”
“acctg_categories”

I want the client to be GROUP, the amount SUM, and the “categoryID” to be “0” to filter for a specific form of income. This is filtered with Date being a date range.

Everything works fine until I tell the “categoryID” to criterion “0”…here’s the SQL statement off the designer window:

Edit SQL by Ratslinger for easier read:

SELECT "clients"."client_name" AS "client",
       SUM("accounts_receivable"."amount") AS "amount"
FROM "accounts_receivable",
     "clients",
     "acctg_categories"
WHERE "accounts_receivable"."clientID" = "clients"."clientID"
AND   "accounts_receivable"."categoryID" = "acctg_categories"."categoryID"
AND   "accounts_receivable"."Date" BETWEEN:Enter_Begin_Date AND:Enter_End_Date
AND   "acctg_categories"."categoryID" = 0
GROUP BY "clients"."client_name"
ORDER BY "accounts_receivable"."client" ASC

Very hard for me to read; my apologies, as I get lost in the SQL statement itself. I didn’t type it; Libreoffice wrote it as I designed the Query in the GUI. What makes it harder is trying to understand what Libreoffice is telling me in the error.

The error is: Not in aggregate function or group by clause: org.hsqldb.Expression@1186c88

Using the “more” dialog button I get this:
SQL Status: 37000
Error code: -67

which then says what the error is and then pastes all the code in one big ugly paragraph.

What am I missing? I REALLY appreciate any help anyone can give.

1 Like
#2

Hello,

A sample Base file would be of great help.

Edit:

The line I don’t like is:

AND   "accounts_receivable"."Date" BETWEEN:Enter_Begin_Date AND:Enter_End_Date

since between uses AND and then the next line has another AND.

Try:

AND   ("accounts_receivable"."Date" BETWEEN:Enter_Begin_Date AND:Enter_End_Date)

It isolates by enclosing with parentheses.

#3

Set up test and found this last line:

ORDER BY "accounts_receivable"."client" ASC

That field does not exist. If changed to:

ORDER BY "clients"."client" ASC

then all works:

Screenshot at 2022-01-27 17-32-38
Screenshot at 2022-01-27 17-32-381042×589 106 KB

tested with start date of 03/01/21 and end or 03/15/21

If you still have issues, going to need sample Base file (scrubbed) showing your problem.

#4

Hi Ratslinger,

I apologize as I didn’t even see where I could upload a sample/scrubbed db; nor did it even come to mind to do it. –

Thank you so much for helping me out with this. Here’s my scrubbed and watered down db.
I did the recommendations you provided, but they didn’t do anything. Still got the error. Incidentally, I got a Syntax error when I put those parenthesis in the SQL writer for the query. Again, not overly familiar with writing SQL but am familiar with the Query build GUI as pictured here.
Here’s the db…
DRK Music Inc-sample.odb (52.6 KB)

…and here’s a screen print of what I’ve got going on: (Query, “AR Income Summary Query beta”, start date 01/01/21, end date 12/31/21)

query build error
query build error1167×879 44.1 KB

(The dates should’ve come up, but because I forgot to check as “viewable” that’s neither here nor there. )

#5

So please help me out here. The screen print and SQL are different from that in the provided sample. In the provided sample the error is this line:

ORDER BY AVG("accounts_receivable"."client") ASC

But no GROUP BY statement

Some direction here please.

#6

it changed when I put it through again to recreate the error. No clue why. But please use the sample db. I just used the db’s GUI to design the Query as shown in the picture…please note where I put the Group, Sum, and criterion.

Guessing since it error’ed out, it didn’t save my attempted functions/criterion.

#7

This is not proper - what is the purpose?

#8

I don’t understand AVG – isn’t that average? I didn’t ask it to average anything out. Basically what I need is what I put in the GUI.

Group by client name and SUM so multiple entries for that client shows up in the Query as 1 row.
ie: client1 $50
client 1 $100
client 1 $150

returns in the query as
client 1 $300

then the next line shows
client 2 $700

and the next line shows
client 3 $120

and so on.

But I need the Income category to be just categoryID “0” (ie: job income). And THAT’S what seems to be throwing everything out of whack.

Because I have client 4 with categoryID “2” showing a different type of income (ie: rental income) at $50. I don’t want that on the same report because it’s not Job income.

So I need a QUERY that returns and SUMS each client with the “categoryID” of “0” for a date range.

Apparently, via the GUI of the QUERY DESIGNER, I can get only one or the other…not both. I get the aforementioned error when I try both.

I hopefully am missing something. Because this tool is a great tool.

Maybe I should try to write 2 queries…one that does the Group and SUM for me, then build another query using the first query to filter out the categoryID? Can that be done?

If you look at the sample db, you’ll see two queries in there…the “old” is functional. The “beta” is what I’m trying to replace it with so I can build different reports to each query.

#9

It is in the sample. And now waht is this new condition:

This is getting confusing. Your original post and the image posted show a SUM wanted. But your last comment wants multiple entries in one row?

I avoid this whenever I can. SQL is the only way to get good results.

I believe my earliest sample is the answer. Will est with your DB and see.

#10

Sorry to confuse you. I honestly don’t know or understand why the error shows AVG – it was never selected.

To answer your first question, no I don’t want multiples of “client 1” to show up. I want it to group by the client and sum them per client.

Row 1 - client 1 and it’s total for the dates entered
Row 2 - client 2 and its total for the dates entered
and so on

BUT I want it to filter for the CategoryID of “0”.

#11

I went back to my original post; I don’t see AVG in the code

#12

Not in original post but in sample along with other items.

With your sample data and including all dates is this what is wanted:

Screenshot at 2022-01-28 16-57-00

Only catgoryID = 0

Edit:

Based on all stated thus far here is your sample back with an additional query - the one that produced the above result.
DRK Music Inc-sample.odb (52.4 KB)

The Query is named AR Income Summary Query beta Fixed

It is the SQL in your original post plus my correction noted in the second comment:

No other changes. Don’t understand how that did not work for you.

1 Like
#13

image

#14

image

tried running it again

looking over what you said, it appears that LibreOffice is changing the code and removing the “clients” and putting in “accounts_receivable”.

1 Like
#15

what is this? Not what I submitted! Edit: LO can’t change that code. It doesn’t/hasn’t on any of my systems!.
As a bug goes, LO did change the code and this is a bug. I will avoid this term in the future as best I can. End edit.
.
How are you running this?

#16

got me!
windows 10 home.

I didn’t change anything

I changed it back and it works
no CLUE how it changed.

so I guess I’m set – thank you. Although I’m confused…wish me luck on the operating db. I’m just gonna copy/paste the SQL code you did and run with it.

Thank you –
alanon

#17

thank you –
weird…if I go in to EDIT the query in design view, but don’t do ANY changes, it errors out. But if I run the query straight up, it works.

something’s funky with this edition of LO. Running 7.1.8.1.

1 Like
#18

I figured it out. You gave an alias to "clients"."client_name" of “client” which is a field in “accounts_receivable”.
QUERY DESIGNER ignored the alias and used the one found in “accounts_receivable”. A bug in the designer.

Stick with SQL. It is safer.

Edit:

This may be related to tdf#141579

1 Like
#19

ok – unfortunately, I’m a dunce with SQL language. My only ability to build queries has been the QUERY DESIGNER.

:frowning:

thank you for all your help though – I REALLY appreciate it

#20

If you are learning Base you can easily learn SQL - one step at a time. In the meantime, just use a different alias. My quick test shows it works if names are different.

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.