GnuCash SQL Examples

Introduction

This page is under construction.

Request all transactions like shown in the account list

This request gives you all transactions, but transactions, which involve more than two accounts, will show up in the wrong way. If s1.value_num is replaced by s2.value_num, you will have the right amounts, but wrong signed.

select distinct s1.value_num, s1.value_denom, t1.post_date, t1.description,
a1.description, a1.name, a1.account_type,
a2.description, a2.name, a2.account_type
from splits s1
inner join splits s2 on s1.tx_guid=s2.tx_guid
inner join transactions t1 on t1.guid=s2.tx_guid
inner join accounts a1 on s1.account_guid=a1.guid
inner join accounts a2 on s2.account_guid=a2.guid
where a1.name!=a2.name;

Request all child accounts of a given account

This request will result in a list of the start account and all children and grand*x*children. The snippet is based on a snippet from sql-workbench.

with recursive account_tree as (
   select guid, parent_guid
   from accounts
   where guid = 'Start account'  -- this defines the start of the recursion
   union all
   select child.guid,
          child.parent_guid
   from accounts as child
     join account_tree as parent on parent.guid = child.parent_guid -- the self join to the CTE builds up the recursion
)
select * from account_tree;