How can I create a running total for Quantity and Amount columns? I want to build something similar to https://www.adjustedcostbase.ca/, but I added general ledger accounts to do accounting entries as well. Once the running totals work for these columns, I need a calculated column in the query for capital gains/losses related to “Sell” transactions only.
Investments.odb (19.1 KB)
HSQLDB embedded is not well suited for this task.
to get the running totals I made 3 queries in this order: qRownumSetup, qRownum and qRunningSum.
it was necessary to create the views (vRownumSetup and vRownum) from 2 of the queries.
I am simply answering the question and did not hit the link you posted.
I have no idea what that means?
Investments_2.odb (18.9 KB)
I appreciate your help on this matter. It seems the running total works when all rows are displayed by using qRunningSum query. However, when a filter is applied (see qRunningSumFiltered which I created from the previous query by adding where h.“HoldingSymbol” = ‘VCE’ , the running totals are no longer correct.
Investments_3.odb (19.1 KB)
usually the difference to the price at time when the position was bought. In a simple case he’d need a sub-SELECT to find at what time the position was bought by MIN(date).
.
But if we take partial buying/selling into account it gets complicated:
I buy 7 apples, one on each day at increasing prices from 1 to 7 dollar, so paying 28 $ - when I sell 2 apples for 8$ I don’t know is I sold the first two winning 5 $ or the last two loosing money - or am I fully balanced because 28/7 is 4?
@catgiu,
a running total is always calculated from a specific data-source or sources.
the data-sources in this case are the linked tables “Transaction” and “TransactionDetail”.
fields may be edited and records may be inserted or deleted from either table but the running sums will continue to produce accurate totals.
we can alter the data but the data-source is fixed.
.
it’s easy to calculate running totals from a query.
create a view from the query and use it as the data-source.
pen 3 queries as I did with (qRownumSetup, qRownum, qRunningSum) and save using unique names.
.
the long term objective:
create a view from a query which shows every notable field.
create a filter table.
create a form from which you can select preferred filter options.
create a query which references both your saved view and filter table, incorporate the running sum, then use the query as the data-source in a table control.
Thank you very much for dedicating your time, energy and skills to provide a simple and elegant solution. It is still a learning curve for me in regard to some of these objectives but I am determined to learn and finish the project. Would you recommend me some LO tutorials that can quickly set me on the right path? Also, you mentioned that other database engines might be better suited for such tasks. Perhaps, Postgresql with LibreOffice as front end?
I know very little about tutorials but this Base tutorial uses HSQL and is highly recommended by many:
From Newbie to Advocate in a one, two… three!
.
most if not all database user manuals are aimed at knowledgable developers, they simply show how to perform a known task in their own particular dialect and are not tutorials.
.
Postgresql provide many excellent tutorials, you will find them extremely useful no matter which database you use.
.
database choice is a personal issue, they all have pros and cons.
.
most new users will opt for embedded HSQLDB or embedded Firebird, I only use them on this site.
.
the default embedded HSQL is around 16 years old, it cannot handle date or time arithmetic and lacks many of the functions we now take for granted.
modern versions of HSQL contain a wealth of tried and tested functions but lack the window functions which most other open source databases have.
.
I don’t like Firebird it is not user friendly, lacks some of the less popular functions and some of the functions it does have are underdeveloped.
it does however enable date/time arithmetic and has window functions.
if I were developing your project then I would choose Firebird over HSQLDB.
.
Postgresql is my personal favourite but is probably a little over the top for the inexperienced user, the project is superbly managed and maintained.
Concerning LibreOffice-Base one address should always be the guide for Base on LibreOffice.org, as it is dealing with a lot of typical problems from connecting to databases to working around limits of hsqldb or the report-engine. It is not an guide to SQL, as Base can connect to several databases. Most examples are for hsqldb.
English documentation | LibreOffice Documentation - LibreOffice User Guides
we needed 1 query and 2 views when using embedded HSQLDB.
I converted your database to embedded Firebird 3.0.7 which needed 1 query and 1 view.
I converted your database to Postgresql which required just 1 small query and zero views.
EDIT: I was wrong, the code posted below also runs perfectly in Firebird therefore Firebird requires 1 query and zero views.
.
earlier today I browsed the docs for Firebird 4.
Firebird does not conform to sql standards and the improvements made to their window functions fall short of what was promised.
.
I think the best advice is to continue to develop your database using HSQLDB.
.
if you find development rewarding and intend to get serious then do download and install Postgresql, it will take some time to get to grips with but the effort should be worth it.
.
here is the small query used by postgres:
select
td."LineID",
t."TransactionID",
t."TransactionDate",
t."TransactionType",
t."TransactionDescription",
t."TransactionNotes",
a."GLNumber",
a."GLDescription",
h."HoldingSymbol",
td."Amount", td."Quantity", td."Price",
sum("Amount") over(order by t."TransactionDate", td."LineID") "AmountSum",
sum("Quantity") over(order by t."TransactionDate", td."LineID") "QuantitySum"
from
"TransactionDetail" td
left join
(select "TransactionID","TransactionDate", "TransactionType", "TransactionDescription", "TransactionNotes" from "Transaction") t
on td."TransactionID" = t."TransactionID"
left join
"GLAccounts" a
on td.glid = a.glid
left join
(select "HoldingID", "HoldingSymbol" from "Holding") h
on td."HoldingID" = h."HoldingID"
order by t."TransactionDate", td."LineID"…hmmm - not something like:
select CCC."LineID",S, avg(Q) from(
select distinct AA.* , BB.Q from(
select distinct A."LineID", sum(B."Amount")as S from "TransactionDetail" as A
join
"TransactionDetail" as B
on A."LineID" >= B."LineID"
group by A."LineID"
)as AA
join
(select distinct A."LineID", sum(B."Quantity")as Q from "TransactionDetail" as A
join
(select * from "TransactionDetail" where "Quantity" is not null)as B
on (A."LineID" >= B."LineID" )
group by A."LineID"
)as BB
on (AA."LineID" = BB."LineID" )
)as CCC
group by "LineID", S
?
works in LO:(embedded)HSQLDB also, if you really want to cramp your style 
?
(!A_FireBird_User!)
ps:one real PITA about Firebird is having to construct tedious code instead of a simple function to produce reliable list()(order by…) segments, but aside from the other quirks…!LoveYouFirebird!
@GNK,
the OP requires a running total in chronological order but you based your calculations on “LineID” and therefore your results are inaccurate.
.
luckily for you “LineID” (auto value primary key) is almost in step with chronology so purely by chance you did get quite a few correct values.
.
maybe you could update your code to show the date.