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.)
Thanks for your response.
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
@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.
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.
Thanks for your response.
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.
Open a spreadsheet.
menu:Data>Pivot Table>Create…
[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?`
SELECT “your item”, SUM “your amount” FROM "your account`
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
Thanks for your response.
Actually, I need the date in there to do what I’m trying to do. But what it looks like is that I need to learn more about extensions and how to incorporate them into my queries.
M.