Totals summary?

[alensiljak]

Hi!
Is there a way to display column totals at the end of the report?
I.e., if I run

balances where account ~ 'leave' and date <= 2025-05-01

and get

           account              SUM((po
------------------------------  -------
Assets:Vacation:Annual-Leave    xy BD
Assets:Vacation:Approved-Leave  ab BD

are there any options to show, in the same report, the total for the sum() column?

I can get the total by running

select sum(position) where account ~ 'leave' and date <= 2025-05-01

but I think this would be a good feature to have, if it does not already exist.

[blais]

I've thought about this in the past, for any query.
It's not obvious as it really doesn't fit the SQL model.
I think writing a custom script is probably best for that at the moment, but it's a common thing to want.

[blais]

Oh, see this precedent for ROLLUP: https://g.co/gemini/share/d47a1d9dd61a

[alensiljak]

I've thought about this in the past, for any query. It's not obvious as it really doesn't fit the SQL model.

Yes, I thought the same. But... I think that bean-query produces the headers which I don't think I get when I run the query from Cashier Server. Hence the idea for such things to be appended on top of the results, in the similar manner.
However, specifying that one wants these or not is not straightforward, then, I agree.

[mlell]

@alensiljak , @blais I have implemented row and sum totals using GROUP BY ROLLUP(...) or GROUP BY CUBE(....), followed by a PIVOT ON in my fork (commit 6231ac9 as of today). It has no tests yet, so I have not submitted a PR, but if you want to give it a look, I would be happy :-)

Column- and rowwise totals can be obtained like this:

SELECT yearmonth(date) as d,account,sum(position) where account ~ 'Expenses' and date > parse_date('2025-06-01') GROUP BY CUBE (d, account) PIVOT BY account,d

[blais]

I have no cycles, maybe Daniele will be able to look at it

[mlell]

If you have time, I would be glad if you would review if this pull request goes in the right direction! #265