Hi Peter,
Since `other_accounts` is a set (often with a single item) you can make it
hashable by turning it into a comma-joined string with JOINSTR:
beancount> SELECT account, JOINSTR(other_accounts) AS other_accounts,
sum(units(position)) WHERE account ~ "^Assets:Checking" GROUP BY 1, 2 ORDER BY
3, 1, 2
account other_accounts sum_units_po
--------------- ---------------------------- ------------
Assets:Checking Expenses:Rent -2000.00 USD
Assets:Checking Expenses:Groceries -253.96 USD
Assets:Checking Assets:Cash,Expenses:ATMFees -202.50 USD
Assets:Checking Expenses:Utilities -184.30 USD
Assets:Checking Expenses:Transit -98.00 USD
Assets:Checking Income:Wages 3000.00 USD
Another way to write this if you want to report the other side would be to use
FINDFIRST:
beancount> SELECT account, sum(units(position)) WHERE FINDFIRST('Assets:Che.*',
other_accounts) != NULL ORDER BY 2
account sum_units_po
------------------ ------------
Income:Wages -3000.00 USD
Expenses:ATMFees 2.50 USD
Expenses:Transit 98.00 USD
Expenses:Utilities 184.30 USD
Assets:Cash 200.00 USD
Expenses:Groceries 253.96 USD
Expenses:Rent 2000.00 USD
Or use IN with an exact account name
beancount> SELECT account, sum(units(position)) WHERE 'Assets:Checking' IN
other_accounts ORDER BY 2
account sum_units_po
------------------ ------------
Income:Wages -3000.00 USD
Expenses:ATMFees 2.50 USD
Expenses:Transit 98.00 USD
Expenses:Utilities 184.30 USD
Assets:Cash 200.00 USD
Expenses:Groceries 253.96 USD
Expenses:Rent 2000.00 USD
I’m using this file for examples:
% cat example.beancount
plugin "beancount.plugins.auto"
option "operating_currency" "USD"
2022-11-04 * "ACME Corporation" "Net Pay"
Assets:Checking 1,500.00 USD
Income:Wages
2022-11-05 * "ACME Groceries" "Weekly Grocery Shop"
Assets:Checking -154.20 USD
Expenses:Groceries
2022-10-05 * "ACME Cash" "ATM Withdrawal"
Assets:Checking -202.50 USD
Expenses:ATMFees 2.50 USD
Assets:Cash 200.00 USD
2022-11-10 * "ACME Water Power and Sewer" ""
Assets:Checking -184.30 USD
Expenses:Utilities
2022-11-12 * "ACME Groceries" "Weekly Grocery Shop"
Assets:Checking -99.76 USD
Expenses:Groceries
2022-11-18 * "ACME Corporation" "Net Pay"
Assets:Checking 1,500.00 USD
Income:Wages
2022-11-19 * "Supermercado ACME" "Weekly Grocery Shop"
Assets:Cash -200.00 USD
Expenses:Groceries 198.00 USD
Expenses:Gambling 2.00 USD ; Lotto ticket
2022-11-29 ! "ACME Transit" "Transit Pass"
Assets:Checking -98.00 USD
Expenses:Transit
2022-11-30 ! "ACME Property Management" "Rent" #scheduled
Assets:Checking -2,000.00 USD
Expenses:Rent
HTH,
Kevin
> On Nov 19, 2022, at 01:12, Peter <[email protected]> wrote:
>
> I'd like to fetch the sum (per account) of the amount of money I sent from my
> checking account to any account during the year. The goal is to compute the
> average outflow per month to get a better understanding of the cashflow.
>
> I tired:
>
> SELECT account, other_accounts, sum(units(position)) WHERE account ~
> "Assets:Cash:Checking" group by account, other_accounts
>
> or
>
> SELECT account, other_accounts, units(sum(position)) WHERE account ~
> "Assets:Cash:Checking" group by account, other_accounts
>
> and variations of that. Each query failed by telling me
>
> > TypeError: unhashable type: 'list'
>
> I guess, there's a problem calculating a hash or so, but I don't know. Tried
> to search in this group, but I didn't find anything related that helped (I'm
> pretty sure I used the wrong search term, tbh).
>
> How do I get the summarized outflow from the checking account to any other
> account using bean-query?
>
> --
> 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/ec5b7887-63d9-49a3-80f7-5ee317013db6n%40googlegroups.com.
--
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/F46FF835-E709-4D49-AA55-92331007EC51%40gmail.com.
