Obtain Balance in CoA using SQL

Hi,

Is it possible to obtain the balance of an account (similiar to the right of the Chart of Account Tree) using SQL queries only?

I need to pull these balances appearing on the right in the CoA Tree to a Business Intelligence/Reporting App and it only takes SQL queries.

Thanks & Regards
Said

PS. And if it is how would the sql be? and or what tables/fields would be used

Trial Balance report?

Yes, if I was viewing from within ERPNext, however, I’m pulling the data from the DB for use in an BI app.

Regards
Said

If you are looking balance only of leaf node accounts, then it will be like:

select
	sum(debit) - sum(credit)
from
	`tabGL Entry`
where
	account = 'your-account-name'

If you want balance for a customer/supplier, then:

select
	sum(debit) - sum(credit)
from
	`tabGL Entry`
where
	party_type = 'Customer'
	and party = 'your-party-name'

If you looking for group accounts, then you need to pass conditions based on lft and rgt. Check following function for more details:

5 Likes

@nabinhait

Awesome, that’s exactly what I was looking for.

Thank you soo much

Regards
Said

1 Like