Hi Kevin, thanks for your suggestions. They seem to work just fine and to be just what I was looking for.
The SELECT account, sum(units(position)) WHERE 'Assets:Checking' IN other_accounts ORDER BY 2 is really beautiful. It is easy to read and understand, and it solves (at least that's what I'm suspecting) the "problem" with multiple postings in one transaction—as it tackles the task exactly the other way around. Thanks! [email protected] schrieb am Sonntag, 20. November 2022 um 12:57:55 UTC+1: > 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/8c608f85-c6e0-49eb-b684-e45a93addbf7n%40googlegroups.com.
