Thanks! On Friday, May 27, 2022 at 7:14:42 PM UTC+2 [email protected] wrote:
> On 27/05/2022 13:11, Philippe Dumonet wrote: > > Hey, another thing: how do I access inventories in the filter (WHERE) > > part of my queries? I've looked at the help provided from within the > > interactive query command line and there seems to be a bunch of simple > > functions that take an inventory as an input but I can't seem to find > > the relevant column. It keeps telling me "Invalid column name > > 'inventory' in WHERE clause context" and I can't access my own columns > > or use sum within the filter part. > > Aggregates cannot be used in the WHERE clause as the WHERE clause is > executed to filter the rows before computing the aggregates. To filter > on aggregates you would need to specify an HAVING clause, but it is not > implemented in bean-query as included in Beancount v2. > > Working toward Beancount v3, some parts of Beancount have moved to > separate projects. This is the case of bean-query, which has moved to > its own repository https://github.com/beancount/beanquery/ > > beanquery implements the HAVING clause and the empty() BQL function that > checks whether an inventory is empty, see > https://github.com/beancount/beanquery/issues/36 > > beanquery can be installed with Beancount v2 and will replace the > bean-query command with the newer version. A release has not been tagget > yet, thus you would need to install from git. beanquery is evolving > rapidly (at least in comparison to the time I have to dedicate to it) > and the test coverage is not perfect yet, thus some bugs may sneak in. > Please give it a try and report any issue you find. > > > You can find the example ledger file I'm experimenting with here > > <https://pastebin.com/LXYZ1Z05>. The query I'm running is "SELECT > > account, cost_date as date, sum(position) as position, > > cost(sum(position)) as cost, cost_number as num WHERE currency != 'EUR' > > GROUP BY account, currency, cost_date, cost_number". I'm trying to > > filter out rows which have no inventory. > > I would write this like this: > > SELECT > cost_date AS date, > sum(position) AS position, > cost(sum(position)) AS cost > WHERE > account = 'Assets:Crypto' > GROUP BY > currency, > cost_date > HAVING > not empty(sum(position)) > ORDER BY > date > > > 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 on the web visit https://groups.google.com/d/msgid/beancount/7363e876-3bf5-4408-b32f-fdb82542cbban%40googlegroups.com.
