bean-query: running total of group by

[Martin Michlmayr]

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.

--
Martin Michlmayr
https://www.cyrius.com/

[Martin Blais]

Special column named "balance"

--
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 beancount+...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/beancount/20251020022907.GA2661113%40panya.cyrius.com.

[Martin Michlmayr]

Doesn't work with "GROUP BY":

beanquery.compiler.CompilationError: all non-aggregates must be covered by GROUP-BY clause in aggregate query: the following targets are missing: "balance"

That's why I'm asking; or maybe that's a bug I should report.
Daniele?

* Martin Blais <bl...@furius.ca> [2025-10-19 23:42]:

> To view this discussion visit https://groups.google.com/d/msgid/beancount/CAK21%2BhMcdg49147ix5Z7zZV0MQmMYM5tFM4SM6OPEc2cDGg%3Dew%40mail.gmail.com.

[Martin Blais]

Ah yes...

To view this discussion visit https://groups.google.com/d/msgid/beancount/20251020034454.GA2665429%40panya.cyrius.com.

[Stefano Zacchiroli]

On Mon, Oct 20, 2025 at 10:29:07AM +0800, 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.

I never found a way either. My common workaround is to have two queries,
one with the details like yours and one with the grand total at the end.
(And it is indeed a limited workaround, as it only shows you the final
total.)

Cheers
--
Stefano Zacchiroli - https://upsilon.cc/zack
Full professor of Computer Science, Polytechnic Institute of Paris
Co-founder & CSO Software Heritage

[Chary Ev2geny]

I resolve this by importing results of beanquery in pandas dataframe and doing all further manipulations there.

[Daniele Nicolodi]

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

[Daniele Nicolodi]

On 20/10/25 05:44, Martin Michlmayr wrote:
> Doesn't work with "GROUP BY":
>
> beanquery.compiler.CompilationError: all non-aggregates must be covered by GROUP-BY clause in aggregate query: the following targets are missing: "balance"
>
> That's why I'm asking; or maybe that's a bug I should report.
> Daniele?

The fact that you get a Python exception instead of a nice error message
is a (minor) bug. However, what the Python exception says is true:
columns in an aggregate query should either be aggregate functions or be
part of the grouping definition. Anyway, the "balance" column would not
do what you want.

Cheers,
Dan

[Martin Blais]

I approve of this message

--

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 beancount+...@googlegroups.com.

To view this discussion visit https://groups.google.com/d/msgid/beancount/0db38d1a-64f7-477a-b3d4-d7f73b264d31n%40googlegroups.com.