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.
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.
This is not proper - what is the purpose?
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.
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.
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”.
I went back to my original post; I don’t see AVG in the code
Not in original post but in sample along with other items.
With your sample data and including all dates is this what is wanted:
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.
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”.
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?
got me!
windows 10 home.
I didn’t change anything
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
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.
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
ok – unfortunately, I’m a dunce with SQL language. My only ability to build queries has been the QUERY DESIGNER.
thank you for all your help though – I REALLY appreciate it
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.
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.
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"
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.
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.
ok thank you. that makes sense.