how to get the number of transactions with BQL

[Triple Z]

I have tried BQL like `select count(1) from year >= 2022` but I only get the number of postings but not transactions.

Any idea to do that?

[Martin Blais]

I don't thin you can run that one through SQL.

Just write a little script producing stats

Like one of those: https://github.com/beancount/beanlabs/tree/master/beanlabs/scripts

On Tue, Oct 18, 2022 at 11:09 PM Triple Z <zhaozhen...@gmail.com> wrote:

I have tried BQL like `select count(1) from year >= 2022` but I only get the number of postings but not transactions.

Any idea to do that?

--
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 on the web visit https://groups.google.com/d/msgid/beancount/272b8116-593d-47b2-b043-17423badcb7bn%40googlegroups.com.

[Daniele Nicolodi]

If you use beanquery:

SELECT
count(1)
FROM
#entries
WHERE
type = 'transaction' AND
year >= 2022

or

SELECT count(id) FROM (SELECT DISTINCT id WHERE year >= 2022)

Both solutions requires a fixes I pushed just a few minutes ago.

SELECT
count(date)
FROM
#entries
WHERE
type = 'transaction' AND
year >= 2022

should work also with a somehow older checkout.

This reminds me that I need to make COUNT(*) work and that I need to add
a #transactions table (probably after some code reorganization).

Cheers,
Dan

[Martin Blais]

Thanks Daniele,

FWIW, One thing I dreamed about a long while ago was a change to BQL to expose the various beancount data as multiple tables, postings, transactions, balanced, events, and more. I think it's more SQLish ("squealish"?) than the semantics I attached to FROM and TO. What you've done here is in that spirit, I love it. Thank you!

--
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 on the web visit https://groups.google.com/d/msgid/beancount/2a3218c2-a9e2-c905-6194-892a2ee84d1b%40grinta.net.

[Daniele Nicolodi]

On 22/10/2022 03:54, Martin Blais wrote:
> Thanks Daniele,
>
> FWIW, One thing I dreamed about a long while ago was a change to BQL to
> expose the various beancount data as multiple tables, postings,
> transactions, balanced, events, and more. I think it's more SQLish
> ("squealish"?) than the semantics I attached to FROM and TO. What you've
> done here is in that spirit, I love it. Thank you!

The infrastructure for exposing Beancount data as multiple tables (and
to allow beanquery to operate on arbitrary data) is all there. Before
adding more table definitions, I would like to reorganize the code to
better isolate the Beancount specific parts.

For backward compatibility, if a table is not specified in the FROM
clause, beanquery operates on the "postings" table. Expressions in the
FROM clause are still supported and are merged with the WHERE clause at
query compilation time. This requires a way to disambiguate between
column names part of an expression and table names in the FROM clause
and is the reason why table names need to be prefixed with "#".

Cheers,
Dan

[Stefano Zacchiroli]

On Sat, Oct 22, 2022 at 02:27:53PM +0200, Daniele Nicolodi wrote:
> For backward compatibility, if a table is not specified in the FROM clause,
> beanquery operates on the "postings" table. Expressions in the FROM clause
> are still supported and are merged with the WHERE clause at query
> compilation time. This requires a way to disambiguate between column names
> part of an expression and table names in the FROM clause and is the reason
> why table names need to be prefixed with "#".

This is all very exciting, thanks for your work on this!

User question: will the new #tables be integrated in the documentation
at https://beancount.github.io/docs/beancount_query_language.html or
should we (users) look somewhere else? I feel like BQL is moving fast
and I'm not yet sure where to look for up-to-date doc. TIA!

--
Stefano Zacchiroli . za...@upsilon.cc . upsilon.cc/zack _. ^ ._
Full professor of Computer Science o o o \/|V|\/
Télécom Paris, Polytechnic Institute of Paris o o o </> <\>
Co-founder & CTO Software Heritage o o o o /\|^|/\
Former Debian Project Leader & OSI Board Director '" V "'

[Daniele Nicolodi]

On 22/10/2022 18:32, Stefano Zacchiroli wrote:
> On Sat, Oct 22, 2022 at 02:27:53PM +0200, Daniele Nicolodi wrote:
>> For backward compatibility, if a table is not specified in the FROM clause,
>> beanquery operates on the "postings" table. Expressions in the FROM clause
>> are still supported and are merged with the WHERE clause at query
>> compilation time. This requires a way to disambiguate between column names
>> part of an expression and table names in the FROM clause and is the reason
>> why table names need to be prefixed with "#".
>
> This is all very exciting, thanks for your work on this!
>
> User question: will the new #tables be integrated in the documentation
> at https://beancount.github.io/docs/beancount_query_language.html or
> should we (users) look somewhere else? I feel like BQL is moving fast
> and I'm not yet sure where to look for up-to-date doc. TIA!

Documentation is the main reason why I haven't published a release of
beanquery yet. I plan to provide updated documentation but I haven't
published anything yet.

I have a local branch that moves the documentation to Sphinx. I started
looking into writing a Sphinx plugin to auto generate documentation for
BQL tables and functions. However, I am not familiar with Sphinx and I
don't have enough uninterrupted hacking time to make progress. Help with
this would be greatly appreciated.

Cheers,
Dan