Rethinking Numeric

John Ralls jralls at
Tue May 27 19:54:41 EDT 2014

On May 26, 2014, at 12:44 PM, Phil Longstaff <phil.longstaff at> wrote:

> Re numeric in queries...
> At one point, I was playing with not loading all transactions/splits on
> startup.  However, I still needed the account balances.  I remember using a
> (pseudo-sql) query something like:
>    select sum(amount.numerator) from splits where account_guid='the one I
> want' and date < today group by amount.denominator
> I could then add a 'where' clause for reconciliation state and repeat to
> get reconciled balance and another state for cleared balance.  I could have
> gotten more than one value back, with different denominators, but in
> practice that never happened.  If it had, I was prepared with a small loop
> which just added the values together.
> Come to think of it, I might even have removed 'account_guid="the one I
> want"' and grouped by account_guid as well as by amount.denominator so that
> I had an array of results, one per account.
> This never went ahead because of the code in gnucash which assumes all
> splits are in memory, but the code might still be in there somewhere but
> never used.  I can dig further if you want.

Yup, gnc_sql_get_account_balances_slist( GncSqlBackend* be ) in gnc-transaction-sql.c, starting at line 1334.
The actual line is
    buf = g_strdup_printf( "SELECT account_guid, reconcile_state, sum(quantity_num) as quantity_num,
                            quantity_denom FROM %s GROUP BY account_guid, reconcile_state, quantity_denom ORDER BY account_guid,
                            reconcile_state”, SPLIT_TABLE );

I’d actually noticed that a couple of months ago when I was working on the private-kvp branch.

I’m surprised you never saw different denominators, since GncNumerics are supposed to reduce; e.g $0.50 should be 1/2. Even so it would be better than getting a cursor and iterating over every split in an account to get the totals, but not as good as just being able to sum the quantities.

John Ralls

More information about the gnucash-devel mailing list