Query Engine Problem, or Bad Query?

[Jonathan Salles]

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

[Martin Michlmayr]

* Jonathan Salles <jfs...@gmail.com> [2019-04-19 16:56]:

> This query gives the results below:
> SELECT
> account,
> position,
> balance

This will show the correct result:

select account, sum(position) WHERE account ~ 'bank' GROUP by 1
account sum_positio
----------------------------- -----------
Assets:US:Bankspouse:Checking -400.00 USD
Assets:US:Bankself:Checking -200.00 USD

> 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

...

Unfortunately I don't know how to show the individual positions and
the balance.

--
Martin Michlmayr
https://www.cyrius.com/