SELECT [arithmetic_expression] in BQL

[Stefano Zacchiroli]

Is there a way to perform simple arithmetic expression in BQL, e.g.,
additions/differences between other columns?

My use case is queries like this one:

3000-01-01 query "investments" "
SELECT account, UNITS(SUM(position)) AS units, COST(SUM(position)) AS cost, VALUE(SUM(position)) AS value
WHERE account ~ '^Assets:.*Investments'
GROUP BY account, currency
ORDER BY account, currency
"

where I'd like to have an extra column showing "value - cost".

TIA,
Cheers
--
Stefano Zacchiroli . za...@upsilon.cc . upsilon.cc/zack . . o . . . o . o
Computer Science Professor . CTO Software Heritage . . . . . o . . . o o
Former Debian Project Leader & OSI Board Director . . . o o o . . . o .
« the first rule of tautology club is the first rule of tautology club »

[Martin Blais]

On Thu, Feb 14, 2019 at 5:36 AM Stefano Zacchiroli <za...@upsilon.cc> wrote:

Is there a way to perform simple arithmetic expression in BQL, e.g.,
additions/differences between other columns?

No, but it's entirely within scope to implement that eventually.

I've been refraining from more involved features in the SQL query engine because I want to fork this out to a different project eventually and spend all energies there (and for that to replace the current one, with Beancount integration). It would also need to have types as well. Lots of work to do there.

My use case is queries like this one:

3000-01-01 query "investments" "
  SELECT account, UNITS(SUM(position)) AS units, COST(SUM(position)) AS cost, VALUE(SUM(position)) AS value
  WHERE account ~ '^Assets:.*Investments'
  GROUP BY account, currency
  ORDER BY account, currency
  "

where I'd like to have an extra column showing "value - cost".

Sounds reasonable to me.

You're welcome have a go at it.

A workaround could be to export to a CSV file and then process that with a custom script.

Or, if you export to Google Sheets (with upload-to-sheets), you can just do that arithmetic in the Sheets doc directly.

I do much of my post-export aggregation in that fashion.

This is the export I use:

https://bitbucket.org/blais/beancount/src/487cb9b0248427ac6882889b6b8bcc1665ce393e/beancount/projects/export.py?at=default&fileviewer=file-view-default

 

TIA,
Cheers
--
Stefano Zacchiroli . za...@upsilon.cc . upsilon.cc/zack . . o . . . o . o
Computer Science Professor . CTO Software Heritage . . . . . o . . . o o
Former Debian Project Leader & OSI Board Director  . . . o o o . . . o .
« the first rule of tautology club is the first rule of tautology club »

--
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 post to this group, send email to bean...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/20190214103611.zwqcosxaepskwlkv%40upsilon.cc.
For more options, visit https://groups.google.com/d/optout.