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

[alanon]

Thank you Ratslinger!

Unfortunately, being a newbie to the forum, I hit some sort of post limit so couldn’t add this.

Quick question:

I also needed to do a QUERY for the opposite infomation. Using your SQL statement as a template, I did some changes to use for the new QUERY.

This WAS the code I tried, which error’ed out:

SELECT
“clients”.“client_name” AS “client”,
“acctg_categories”.“category”,
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” BETWEEN 1 AND 36

GROUP BY “clients”.“client_name”

ORDER BY “clients”.“client” ASC

---

On a fluke, I changed this line in the GROUP BY with the following; no other changes:

 GROUP BY "clients"."client_name", "acctg_categories"."category" 

And it worked.

My question is, why did I need to add a second GROUP BY criterion to the SQL code for this to work? I personally didn’t need a secondary GROUP BY in my results.

Thank you.

[Ratslinger]

@alanon

why did I need to add a second GROUP BY

Because you selected that field for the result set. Without that in the GROUP BY it make no sense to display - group would only be by client_name.

Edit:

To maybe make this a bit more clear, if Susan is in category 02 & 04, and you only group by name and the total for Susan is 240.32, then how is SQL to determine the amount on the line with Susan 02 and how much on the line with Susan 04? With category in the grouping the correct total will be added for each. If you only want a total for Susan then do not include category in the select.

[alanon]

ok thank you. that makes sense.

[Ratslinger]

@alanon

Any further questions should be a new post otherwise the questions get buried. This is a question and answer site.

Also, here is a site I use for reference at times, it may be of use to you. There are many others also:

w3schools