I think this is what you are looking for SELECT account, SUM(position) WHERE account = "Assets:Checking" OR "Assets:Checking" in other_accounts
the trick is the other_accounts thing The human explanation is that it shows a sum of changes to all accounts by transactions, which involve Assets:Checking You can experiment here: https://colab.research.google.com/drive/1iGqhrQ6UUGCN6BmKDkprtldla6xuB29e?usp=sharing On Friday, September 20, 2024 at 10:15:41 PM UTC+2 Oscar & Alejandra wrote: > Wow, so simple. That is pretty close to what I need, I'm just wondering if > there is a way to filter out only transactions that have a posting with a > specific account. > > For example, take these transactions: > > 2024-01-01 * "Opening Balances" > Liabilities:Credit -1000.00 USD > Equity:Opening 1000.00 USD > > 2024-09-01 * "Salary" > Assets:Checking 3000.00 USD > Income:Salary -3000.00 USD > > 2024-09-02 * "Interest" > Expenses:Interest 10.00 USD > Liabilities:Credit -10.00 USD > > 2024-09-03 * "Credit payment" > Liabilities:Credit 100.00 USD > Assets:Checking -100.00 USD > > 2024-09-04 * "Groceries" > Expenses:Food 200.00 USD > Assets:Checking -200.00 USD > > Running that query will list all the accounts with their balances for that > period, which is great: > > --------------------- -------------- > account SUM(position > > Assets:Checking 2700.00 USD > Expenses:Food 200.00 USD > Expenses:Interest 10.00 USD > Income:Salary -3000.00 USD > Liabilities:Credit 90.00 USD > > But is there a way to only select the transactions that have postings with > 'Assets:Checking'? So it would look like this > > --------------------- -------------- > account SUM(position > > Assets:Checking 2700.00 USD > Expenses:Food 200.00 USD > Income:Salary -3000.00 USD > Liabilities:Credit 100.00 USD > > So I can see everything that came out of my checking account in the given > period? Note that the Liabilities:Credit balance is adjusted to no longer > include the interest charge. > > September 20, 2024 at 1:18 PM, "Chary Chary" <[email protected]> wrote: > > P.S. changes to Equity will also be shown > > > On Friday, September 20, 2024 at 8:16:13 PM UTC+2 Chary Chary wrote: > > Hi, > > > I am not sure exactly what you want to achieve, but the following query > will give you a sum of all changes to to all accounts (including Assets and > Liabilities) over the period of time > > > SELECT account, SUM(position) > WHERE date >=2024-09-12 and date <=2024-09-26 > > This will be all of your incomes, all expenses over the period, as well as > changes to Assets and Liabilities over the same period. > > Total will be 0. > > On Friday, September 20, 2024 at 6:30:29 PM UTC+2 Oscar & Alejandra wrote: > > Hello everyone, > > I am using the latest beanquery from PyPI (v0.1.dev0) and I'm pretty new > to SQL and beanquery's SQL-like syntax. I am trying to produce a report > where I can see all of my expenses for a certain period, something like > what my bank statement would show but grouped by my expense categories. I > found this in the examples in the docs and it's pretty close to what I want > but I'm not sure how to grab the 'transfers' from my asset account. For > example when I make a payment towards my credit card or when I transfer > into my savings. > > FROM OPEN ON 2024-09-12 CLOSE ON 2024-09-26 > SELECT account, SUM(position) > WHERE account~"Income|Expenses" > GROUP BY 1 > ORDER BY 1 > > The above query produces something like the following: > > ----------------------------------- ------------ > account SUM(position > Expenses:Auto:Fuel 50.00 USD > Expenses:Auto:Insurance 140.00 USD > Expenses:Clothes 20.00 USD > Expenses:Debt:Interest 83.00 USD > Expenses:Food:Dining 100.00 USD > Expenses:Food:Groceries 140.00 USD > Expenses:Home:Utilities:Electricity 80.00 USD > Expenses:Home:Utilities:Water 70.00 USD > Income:Salary -3000.00 USD > > Where I would really like something like this > > ----------------------------------- ------------ > account SUM(position > Expenses:Auto:Fuel 50.00 USD > Expenses:Auto:Insurance 140.00 USD > Expenses:Clothes 20.00 USD > Expenses:Debt:Interest 83.00 USD > Expenses:Food:Dining 100.00 USD > Expenses:Food:Groceries 140.00 USD > Expenses:Home:Utilities:Electricity 80.00 USD > Expenses:Home:Utilities:Water 70.00 USD > Income:Salary -3000.00 USD > Liabilities:CreditCard 100.00 USD > Assets:Savings 300.00 USD > > Which would essentially show everything my 'paycheck' was spent on for the > period selected. Is this possible using beanquery? > > Thanks! > > > Oscar & Ale > > [email protected] > > > -- > > 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/12877477-b999-45f6-aaba-042d4c101254n%40googlegroups.com > > <https://groups.google.com/d/msgid/beancount/12877477-b999-45f6-aaba-042d4c101254n%40googlegroups.com?utm_medium=email&utm_source=footer> > . > > > > Oscar & Alejandra Camorlinga > [email protected] > -- 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/d682d34c-6eb5-4183-a390-e9635adb6df4n%40googlegroups.com.
