On 20/10/25 04:29, Martin Michlmayr wrote:
I used the following query to see what I spent on a recent trip:
SELECT account, SUM(CONVERT(position, 'USD', date)) WHERE account ~
"Expenses:" AND 'something' in links GROUP BY 1 ORDER BY 1
I'd like to have a third column that shows the running total of column
two but I can't figure out how.
Running totals in SQL are implemented with windowed aggregation
functions. In this case, the simplest query that comes to mind that does
what you would be something like:
SELECT
account,
amount,
sum(amount) OVER (ROWS UNBOUNDED PRECEDING)
FROM
SELECT
account,
SUM(CONVERT(position, 'USD', date)) AS amount
FROM
#postings
WHERE
account ~ "Expenses:" AND 'something' in links
GROUP BY 1
ODER BY 1
There may be a way to do it without a sub-query but my SQL-foo is
limited, but this kind of simple sub-query are supported in bean-query.
However, unfortunately, bean-query does not support windowed aggregation
functions. It is a long time that I have in my mind to add support for
them but the task is daunting and I never started working on it.
Supporting some simple cases should not be too difficult.
Cheers,
Dan
--
You received this message because you are subscribed to the Google Groups
"Beancount" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion visit
https://groups.google.com/d/msgid/beancount/a0f716b9-6102-402b-b75c-ab4039a4bbc6%40grinta.net.
