SQL - How to Calculate Account Balances in Gnucash?

Question

Title: How to Calculate Account Balances in Gnucash with Recursive SQL

Hello Stack Overflow Community,

I've been using Gnucash for a year and recently attempted to create custom reports for my financial data. However, I encountered a challenge while trying to calculate the cumulative values of accounts, including their sub-accounts.

Database Schema:

I'm working with the following tables in my Gnucash database: (https://piecash.readthedocs.io/en/master/_images/schema.png)

• 'accounts'
• Columns: guid, name, account_type, commodity_guid, commodity_scu, non_std_scu, parent_guid, code, description, hidden, placeholder
• Example data provided:

guid	name	account_type	commodity_guid	commodity_scu	non_std_scu	parent_guid	code	description	hidden	placeholder
1fb9d8623ae04501a62b71dd0d5f1f8d	Root Account	ROOT		0	0				0	0
7c222beb8a7c439399d1ac4850ba09fb	Accruals	ASSET	dbf3687850b3411a8b548d48a6e961ee	100	0	681fe705237c4ba39026ac7ab5b48b58	1.3		0	0
b3f3021120954d1e83c651e6de131e0b	Accounts Receivable	ASSET	dbf3687850b3411a8b548d48a6e961ee	100	0	681fe705237c4ba39026ac7ab5b48b58	1.2		0	0
12ffda3963ab46d4b8f95635c3a1debe	Liabilities	LIABILITY	dbf3687850b3411a8b548d48a6e961ee	100	0	1fb9d8623ae04501a62b71dd0d5f1f8d	2		0	0
3ee7b0171d9b4b76aae67e701ae3ed0e	Accounts Payable	LIABILITY	dbf3687850b3411a8b548d48a6e961ee	100	0	12ffda3963ab46d4b8f95635c3

• 'transactions'
• Columns: guid, currency_guid, num, post_date, enter_date, description
• Example data provided

guid	currency_guid	num	post_date	enter_date	description
e7a4c802ca2b4f90a167e3376199ad97	dbf3687850b3411a8b548d48a6e961ee		##########	##########	Openin Balance - Wallet
387f19734c9042f982f023b754b55fe2	dbf3687850b3411a8b548d48a6e961ee		##########	##########	Public Transport
057ce32f70d44ccdbb043aaa3fa6a54f	dbf3687850b3411a8b548d48a6e961ee		##########	##########	Water
59c8e5fa1a9b45cea20fe1b5453e7743	dbf3687850b3411a8b548d48a6e961ee		##########	##########	Income - Gift
2e34c3b14e464144a30690a7b3ab873e	dbf3687850b3411a8b548d48a6e961ee		##########	##########	Junk Food

• 'splits'
• Columns: guid, tx_guid, account_guid, memo, action, reconcile_state, reconcile_date, value_num, value_denom, quantity_num, quantity_denom, lot_guid
• Example data provided:

guid	tx_guid	account_guid	memo	action	reconcile_state	reconcile_date	value_num	value_denom	quantity_num	quantity_denom	lot_guid
4c8e31391a994309835ccf22041716b7	e7a4c802ca2b4f90a167e3376199ad97	a35d4345406045189f726a23a4d9f6be			n	##########	2000	100	2000	100
96bc3cb0d8a140468d672f8fe6f37ab4	e7a4c802ca2b4f90a167e3376199ad97	1652d2056e9f423c95d187614f60be3e			n	##########	-2000	100	-2000	100
bdd077bbcc0d44d78059b8ac32f07032	387f19734c9042f982f023b754b55fe2	7e71a62c60a1493b9660a35ed287a968			n	##########	1000	100	1000	100
5088a553afbb478e8367a5d751746200	387f19734c9042f982f023b754b55fe2	a35d4345406045189f726a23a4d9f6be			n	##########	-1000	100	-1000	100
d4a62d10713e4c45b58e35c52f707a7f	057ce32f70d44ccdbb043aaa3fa6a54f	47131155460843279406277871f98d6e			n	##########	500	100	500	100

My Goal:

I aim to generate financial reports that summarize the account balances, including sub-accounts. The desired outcome can be visualized as shown in this image: (https://ibb.co/jyPzpXY)

Challenges:

I attempted to achieve this using the following recursive SQL query:

WITH RECURSIVE AccountHierarchy AS
    (SELECT a.guid,
            a.parent_guid,
            a.code,
            a.name,
            0 AS level,
            SUM(COALESCE(s.value_num, 0)) as value_num
     FROM accounts a
     LEFT JOIN splits s ON a.guid = s.account_guid
     WHERE parent_guid IS NULL
     GROUP BY a.guid,
              a.parent_guid,
              a.code,
              a.name
     UNION ALL SELECT a2.guid,
                      a2.parent_guid,
                      a2.code,
                      a2.name,
                      ah.level + 1 AS level,
                      SUM(COALESCE(s2.value_num, 0)) + SUM(ah.value_num) AS value_num
     FROM accounts AS a2
     JOIN AccountHierarchy AS ah ON a2.parent_guid = ah.guid
     LEFT JOIN splits s2 ON a2.guid = s2.account_guid
     GROUP BY a2.guid,
              a2.parent_guid,
              a2.code,
              a2.name,
              ah.level)
SELECT guid,
       parent_guid,
       level,
       code,
       name
FROM AccountHierarchy
WHERE parent_guid IS NOT NULL
ORDER BY code,
         level,
         name;

However, I encountered an error:

ERROR: aggregate functions are not allowed in a recursive query's recursive term

Question:

How can I modify my SQL query or approach to successfully calculate the account balances, including their sub-accounts, as shown in the desired query image? I would greatly appreciate any guidance or suggestions to help me overcome this challenge.

Thank you in advance!

---

This revised version of your question provides a clear and concise description of your issue, includes relevant details, and presents the problem you're facing. It also mentions your desired outcome and the specific error you encountered, making it suitable for Stack Overflow.