[Dropped] Display column sum in report

[pentaauto]

Edit: Split from [Solved] Query to display rows and their sum because that topic is solved so you need your own. If you had the same problem you could use the same solution. Please do not post in another's topic unless you are helping to solve their problem for them. -- MrProgrammer, forum moderator

I need help Getting the SUM of the Colum "TOTAL" in my SQL statement Please:

SELECT
"JOBNO", "CUSTOMER", "LICENSE", "INSURANCE", "TOTAL", "RECEIVED", "NOTES"
FROM "Cumulative_Jobs"
WHERE "JOBNO" = :Enter_JOBNO

[UnklDonald418]

SUM of the Colum "TOTAL" in my SQL statement

Are you trying to get a SUM of all the records in "Cumulative_Jobs" or are there multiple records in the table "Cumulative_Jobs" with the same JOBNO ?
What is the Primary Key in the table "Cumulative_Jobs"?
Aggregate functions like SUM must appear in a separate query or sub=query something like:

Code: Select all

SELECT "JOBNO", "CUSTOMER", "LICENSE", "INSURANCE", "t1" AS  "TOTAL", "RECEIVED", "NOTES"
FROM "Cumulative_Jobs", ( SELECT SUM("TOTAL")  FROM "Cumulative_Jobs" ) "t1" 
WHERE "JOBNO" = :Enter_JOBNO 

OR maybe

Code: Select all

SELECT "JOBNO", "CUSTOMER", "LICENSE", "INSURANCE", "t1" AS  "TOTAL", "RECEIVED", "NOTES"
FROM "Cumulative_Jobs", ( SELECT SUM("TOTAL"), "JOBNO"  FROM "Cumulative_Jobs" WHERE "JOBNO" = :Enter_JOBNO GROUP  BY  "JOBNO" ) "t1" 
WHERE "Cumulative_Jobs"."JOBNO" = "t1"."JOBNO" 

I can't test any of this without a sample database
How to attach a document here

[pentaauto]

The database key is JOBNO and has multiple records for the JOBNO with TOTAL amounts received which is a date. In in my query I want to see
the column that says TOTAL with the sum on the bottom.
Here is what I get currently when I run my query:

JOBNO  CUSTOMER    LICENSE   INSURANCE   TOTAL      RECEIVED    NOTES
52114  MARK LARO   226XL     ARBELLA     $1800.00   08/05/25
52114  MARK LARO   226XL     ARBELLA     $2000.00   08/08/25

I would like to see the sum of the total : $3800.00

[Villeroy]

download/file.php?id=14591

The main form takes filter criteria which substitute the subform's parameters. In order to make this work without macro code, the main form's criteria values need to be stored somewhere. The filter criteria are stored in a distinct record of a separate table:

Code: Select all

select * from "Filter" WHERE FID=1

.
The 4 filter criteria from date, until date, category ID, and person ID are evaluated like this:

Code: Select all

SELECT "PID", "CID", "D", "V", "ID" 
FROM "Data"
WHERE ("PID"=:paramP OR :paramP IS NULL)
AND("CID"=:paramC OR :paramC IS NULL)
AND("D">=:paramD1 OR :paramD1 IS NULL)
AND("D"<=:paramD2 OR :paramD2 IS NULL)

Any missing parameter returns True for the respective condition, ignoring the missing parameter.
The filtered record from the "Data" table is editable because it is not joined with any other table.

There is a second subform which calculates sum, count, average, min, max based on the same criteria stored in "Filter" WHERE FID=1. This subform joins the data table directly with the filter record. The calculation result is read-only anyway. It is possible to do the same trick with a parameter query like the one in the filtered table grid. Just replace the selected field names with SUM(...), COUNT(...) etc.

The reports in that database document use the same filter criteria.

[UnklDonald418]

If you want that total to appear with the results of the query

Code: Select all

SELECT "JOBNO", "CUSTOMER", "LICENSE", "INSURANCE", "TOTAL", "RECEIVED", "NOTES", 
(SELECT SUM( "TOTAL" ) FROM "Cumulative_Jobs" WHERE "JOBNO" = :Enter_JOBNO) GRAND_TOTAL
FROM "Cumulative_Jobs"
WHERE "JOBNO" = :Enter_JOBNO

A Primary Key is a value that uniquely identifies a database record (row), so in this case JOBNO cannot be the Primary Key. In my test database I have a field named ID that serves as the Primary Key Using that I can display a running total

Code: Select all

SELECT "JOBNO", "CUSTOMER", "LICENSE", "INSURANCE", "TOTAL", "RECEIVED", "NOTES", 
( SELECT SUM( "TOTAL" ) FROM "Cumulative_Jobs" "RT" WHERE "JOBNO" = :Enter_JOBNO AND "RT"."ID" <= "Cumulative_Jobs"."ID" ) "RUNNING_TOTAL" 
FROM "Cumulative_Jobs"
 WHERE "JOBNO" = :Enter_JOBNO

The approach shown in the example provided by Villeroy where the the detail is displayed on a table on a Base form and the calculated Sum appears on a Subform is likely the most practical solution. His example also uses a Filter table that would allow the user to pick the JOBNO from a list of valid numbers rather than typing a number into a parameter prompt that may not be valid.

[pentaauto]

The actual database is an openoffice calc (spreadsheet) that base has attached as the database. Does this present the issue ?

[UnklDonald418]

Why are you trying to use a database connection when a spreadsheet can easily make those calculations?
SQLaggregate functions like SUM() are not available through a spreadsheet connection, so for the database to work you would need to import the data into an actual database table with a Primary Key.

[MrProgrammer]

Why are you trying to use a database connection when a spreadsheet can easily make those calculations?

I wondered that too. The WHERE clause In your Base SQL query selects a subset of the data; it's a filter. Calc has filters as well. Here is an example of using a filter in Calc which can show the subtotal of the filtered items. I typically use Data → Filter → Standard Filter when I want to filter, but you can also use AutoFilter and Advanced Filter too. Read about filters in Help → Index or in User Guides (PDF) or searching for topics about this in the Calc Forum.

202508281211.ods

(22.58 KiB) Downloaded 5 times

If you want additional help with Calc, open a new topic in the Calc Forum. Asking further about Calc in the Base forum will cause this topic to be locked. I will not reply to any questions about Calc in this topic. Attach your spreadsheet to the new topic demonstrating your situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the spreadsheet itself). I will not help you in the Calc forum unless you attach your spreadsheet document.

If this solved your problem please go to your first post use the Edit ✏️ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know