Queries are not always editable, and there are a whole bunch of things that can make a query non-editable. Rather than go into that list, which you can google and find for yourself, I suggest you start with a simple query that works, and slowly add to it rather than start with a complex one and then try to figure out why it doesn’t work.
Also I think your questions are too long. Please simplify your questions. Ok, let me tell you what I mean. When things aren’t working, YOU, start by making a copy of the database, then opening the database copy in destroy is ok mode. In this mode you can start to remove things that don’t directly relate to the problem you are having. In your SQL you can remove a whole bunch of fields, till you’re down to just an ID, and two fields to add together, so you can prove out the basic functionality that you need, i.e. you don’t need to list every field in your SQL, just key ones that relate to getting things to work.
This does two things, one it helps you see your own problem more clearly, and removes possibly complications which might be causing issues. Two, it allows you to upload your database here so others can look at it and give more helpful answers to you.
Here is what I’ve noticed. First
"EbaySale",) doesn’t look right to me. It looks like an error.
"A"."Cost" + "A"."Delcost" + "A"."VAT2" AS "TotalCost1"
SELECT "Table1Test"., "Cost" * .2 AS "VAT2" FROM "Table1Test"
Next, I don’t think HSQLDB 1.8 supports sub-queries, at least not very well.
Also you haven’t said anything about what type these fields are. Are they numeric? Do they allow nulls? Nulls anywhere in sums or concatenations often results in a null result. To avoid this you must convert any nulls to zeros (for numeric values) before doing your addition. Or you must ensure that all fields are populated with data. In Access we used this great little nz() function to do this. In MySQL / MariaDB I use the isnull() function. In HSQLDB 1.8 you must use the IFNULL() or COALESCE() functions. So it’s important which database you’re working in. (Always please say which database you’re working in in your question.)
Now this works in HSQLDB 1.8 to add fields. It uses this SQL:
SELECT "ID", "n1", "n2", "n1" + "n2" AS "total" FROM "Table1"
So you probably have an issue in your FROM clause. Without having your table to try this out on, I can’t offer SQL to you that will work. But I suggest you try to get rid of the subquery, or move to a database that fully supports them, like MariaDB, etc.