# Query syntax for subtotals and total in Base

I’m having trouble with the query syntax for subtotals and a total. My research has found what is called an extension “Rollup” and I haven’t been able to get it to work. I have a query that provides subtotals and that is:

SELECT “Item” AS “Item”, SUM( “Amount” ) AS “Amount”
FROM “Counting”
WHERE “Date” = {d ‘2024-02-26’ }
GROUP BY “Item”
ORDER BY “Item” ASC

Any help would be appreciated.

I don’t see the question here. My guess is, you wish to add another line below with a total for all grouped items?
.
You could use a UNION and add another line below like

``````SELECT 'Total:' AS "Item", SUM( "Amount") AS "Amount"
FROM "Counting"
WHERE "Date" = {d '2024-02-26'}
``````

(I corrected smart quotes. Please use code tags, when pasting code here.)

You’re correct in that I’m going for a grand total and your script gives that to me. I need to figure out how to use the UNION instruction to combine your script to mine. I’m new at all this.
What are code tags? I have a lot to learn, including extensions.
M.

See the linked section of This is the Guide…; you might find the entire document useful reading.

The HSql documentation has more technical information on using SQL; in particular for the UNION it says

UNION and other set operations

Multiple SELECT statements joined with UNION, EXCEPT and INTERSECT are possible. Each SELECT is then treated as a term, and the set operation as an operator in an expression. The expression is evaluated from left to right but INTERSECT takes precedence over the rest of the operators and is applied first. You can use parentheses around any number of SELECT statements to change the evaluation order.

So essentially you would have

``````SELECT this, that from TABLE where something
UNION
SELECT another from TABLE where something
``````

IMHO a UNION is only possible when you have the same number of columns, so I would add second column name:

``````SELECT this, that from TABLE where something
UNION
SELECT another, other from secondsource
``````
1 Like

@markamathews,
we do not know which database you use.
libreoffice comes with 2 databases hsqldb 1.0.x embedded and firebird 3.x embedded neither of which contain the ROUNDUP function.
.
Wanderer suggested a solution 12 days ago, did you try it? what was the result?
.
UNION is the way to go.

2. hit Queries icon.
3. hit Create Query in SQL View.
4. paste the sql code.
5. select direct mode (see pic).
6. hit F5 to execute.
.
caveats:
the total amount needs to be the final record shown therefore I have added the field “Level”.
UNION requires direct mode i.e. menu:>Edit>Run SQL command directly (see image)

EDIT 2024-04-17
just remembered that when using embedded hsqldb ORDER BY clause can only be used once and must be last line of code. sql is now updated.
``````select
"Item",
"Amount"
from
(
select
1 "Level",
"Item",
sum("Amount") as "Amount"
from
"Counting"
where
"Date" = '2024-02-26'
group by
"Level",
"Item"

union all

select
2,
'**Total**',
sum("Amount")
from
"Counting"
where
"Date" = '2024-02-26'
) a
order by "Level", "Item"``````

Thanks for your response. And I did try using UNION but it didn’t work for me. This is what I did:
I have 2 queries that work - one gives me subtotals and the second gives me a grand total. I would like to be able to run just one query to get subtotals and a grand total. I connected the two queries with UNION but got an error saying it wasn’t a query. When I figure out code tags I will include the code to show what I did. That won’t be today.
M.

I’m church treasurer and these particular queries will be used to summarize and total weekly donations. I haven’t started looking at reports - that will come, but not yet.
M.

Sorry, I accidentally deleted my suggestion.
Here is the link to the archive of demo files: https://www.mediafire.com/file/350oxbzv91ur1ee/MailMergeTables_embeddedHSQL.zip/file
Read up the demo setup in MailMergeTables_DEMO.txt
Utilizing the capabilities of form letters and combine them with text tables makes it possible to generate invoices and similar documents.

In this particular case, it may be sufficient to query all the donations together with the years and week numbers. `SELECT *, Year("Date")AS"Y", Week("Date")AS"W"`
Then let the report wizard generate a report grouped by weeks.
Somehow (with no macro code nor extension) it is possible to let the report calculate the sums for each week. However, I can’t find any ready-made sample file and can’t remember how to do it. Chapter 6 of the Base Guide explains it all.

One more option:
Create a query with all the required data plus year numbers and week numbers.
[X] Create pivot table from registered data source.
Select the data source (name of your database) and the query.
Drag year and week to [Column Fields].
Drag the amounts to [Data Fields].
Click [OK]
The result will be a table of years, week numbers and sums per week number.

Screen recording creating a pivot table with subtotals from a database query:
https://www.mediafire.com/file/pa36cwdo1s45jzr/DB2Pivot.mp4/file

Hello, and thanks for plain resume. Did you try the sentence without the date criterion?`

You may specify the date gap too by using the BETWEEN criterion:

WHERE “Date” BETWEEN #beginning# #coming to end#

Should you need it for an invoice, then look at the report extension…

Have a nice day