Dumb newbie question.
I have a large table in Base, imported from Access, and I have set up a few queries. Is there a way to add the (integer) entries in one of the columns in a query? Or do I have to use Calc?
If you are using the Query Designer in Base, along the left margin in the lower pane of the designer window look for Function,
On the Function row below the column you want to sum select that box to display a list of available functions. Select SUM from that list.
If you are designing in the SQL mode the following should do the trick
SELECT SUM(âYourColumnNameâ) AS âTotalâ FROM âYourTableNameâ
1 Like
Thanks, but when I do that, either way, I get
The data content could not be loaded.
Not in aggregate function or group by clause: org.hsqldb.Expression@3c0040f8
Iâm running LibreOffice 7.6.7.2 on Windows 10
Says: Your query has more than one field. You want to get a sum of this one field. So what should the database show for the other fields?
Might be you need
SELECT "Table".*,
(SELECT SUM("Columnname") FROM "Table") AS "SumC"
FROM "Table"
So you get the sum in every row of the table.
Might be you will sum the content for one criteria use this:
SELECT "Criteria",
SUM("Columnname") AS "SumC"
FROM "Table"
GROUP BY "Criteria"
1 Like
Oh, I see, thanks. So Iâve done a separate query that just gives the sum, and that works now.
But what I really wanted was an extra row at the bottom that gives the total of a numerical column. Is this possible?
Fred Apple 3
Jim Banana 2
Susan Grape 4
9
SimpleInventory_embedded.odb (54.8 KB)
Have a look at the âEdit Formâ. It has extra subforms showing the sum.
Somehow, it is possible to do something similar with reports, but I always forget how to do that. I use spreadsheet reports, where summing up imported database queries is trivial.
EDIT
Indeed, it is possible to build a query that returns a table like that. That query would be rather complex. A record set is table or query result where each row describes the exact same thing. A total result at the end of a row set does not fit into the row set because a total represents a completely different item than all the item above the total. Database developers keep the stored items apart from the calcuated results. Storage(tables), processing(queries) and representation(forms&reports) are separated from each other.
Nevertheless, you may open my sample database, create a new query in SQL view, turn on menu:Edit>âRun SQL directlyâŚâ, paste and run the following:
SELECT "Articles"."Name", "Input"."Quantity", 0 AS "Order"
FROM "Input", "Articles"
WHERE "Input"."Article_ID" = "Articles"."ID"
UNION SELECT "Articles"."Name", -1 * "Output"."Quantity", 1
FROM "Output", "Articles"
WHERE "Output"."Article_ID" = "Articles"."ID"
UNION SELECT 'Total:', SUM("Quantity"),2
FROM (
SELECT "Quantity" FROM "Input"
UNION SELECT -1 * "Quantity" FROM "Output"
)
ORDER BY "Order","Name"
Base can not hanle UNIONs, therefore we turn on âdirect SQLâ which passes the entire thing over to the HSQL database engine. A UNION merges multiple record sets one under the other. The first SELECT determines the column labels âNameâ, âQuantityâ and âOrderâ and the column types text, integer and integer. It selects the ingoing items âNameâ as text, âQuantityâ AS integer and âOrderâ as integer 0.
Subsequent UNION SELECT statements need to select the same column types in same column order.
The second statement selects the outgoing items as text, integer and integer 1.
The third statement selects the literal string âTotal:â followed by integer sum and the second integer 2.
The integers 1, 2 and 3 in the âOrderâ column make it possible to force the total into the last row by sort order.
The SQL-solution to this is a UNION of a first SELECT with all rows to add and the SELECT above for the total.