Hello All,
I was trying to get a sum of my and my spouse's IRA contributions for the
year. Using the balance column was fine for the total of both, but when I
tried to use the ORDER BY to group the balance, it doubled the balance for
each account. Why did this happen? I eventually used sum(postition) to
get the sum of each account. I used metadata "retirement" to distinquish
the IRA contributions for each year as the contributions can cover multiple
years and may not always stop for the year on 04/15.
The transactions look like:
2018-12-20 * "External Withdrawal Mutual Fund" "Posted"
Assets:US:bank_spouse:checking -100.00 USD
retirement: "2018RothIRA-spouse"
Assets:ZeroSumAccount:Transfers:MutualFund
This query gives the results below:
SELECT
account,
position,
balance
WHERE any_meta('retirement') ~ "2018RothIRA"
AND account ~ "bank"
ORDER BY account
account position balance
-----------------------------------------
bank_spouse 100.00 USD 100.00 USD
bank_spouse 100.00 USD 300.00 USD
bank_spouse 100.00 USD 500.00 USD
bank_spouse 100.00 USD 700.00 USD
bank_spouse 100.00 USD 900.00 USD
bank_self 100.00 USD 200.00 USD
bank_self 100.00 USD 400.00 USD
bank_self 100.00 USD 600.00 USD
bank_self 100.00 USD 800.00 USD
The accounts are grouped properly but the last balance for bank_spouse is
the total for all deposits, the last balance for bank_self is one deposit
less than the total. Is this because my query is incorrect, or is
something else causing this output? If I remove the ORDER BY I get the
correct total balance. (The accounts may be ordered backwards because I
emulated what was happening, changing the account names and values, but the
amounts increase the same as the real query result).
This query works fine, just giving the total for each account:
SELECT
account,
sum(position) as Total
WHERE any_meta('retirement') ~ "2018RothIRA"
AND account ~ "Middle"
ORDER BY account
Is there a problem with the query mechanism, or (more likely) I made a bad
query? Is there a way to have the balance act on each account, or am I
asking something the query engine isn't designed to do?
Thanks
Jonathan
--
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 post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/beancount/abc62277-95c1-4112-98b6-396c5993a401%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
