SQL: the balance sheet

[Wm]

Part 1 of a possibly strange tutorial

this is a gnucash balance sheet that should work with any SQL backend

===
select sum(value_num) from splits
===

the result should be 0

that is how double entry book keeping works, we want our accounts to
balance, some people might view that as a trial balance but it doesn't
matter right now so long as it all comes to nothing.

Part 2 aka that isn't what I was expecting

OK, so you've some accounting knowledge and already know that Income and
Expenditure shouldn't be part of the balance sheet except in summary

No problem, we do need another table though, the gnucash people decided
to call the place where the details of accounts are recorded accounts
(splits isn't so obvious but makes sense later on) so

===
Select
  Sum(splits.value_num),
  accounts.name
From
  splits Inner Join
  accounts On accounts.guid = splits.account_guid
Group By
  accounts.name
===

which will give you a balance for each of your accounts and the name of
the account next to it, we still haven't got our balance sheet, so

let's add account_type and group by that

===
Select
  Sum(splits.value_num),
  accounts.name,
  accounts.account_type
From
  splits Inner Join
  accounts On accounts.guid = splits.account_guid
Group By
  accounts.name, accounts.account_type
===

the fun bit (one of many) is that we don't need the account name, just
the type, so

===
Select
  Sum(splits.value_num),
  accounts.account_type
From
  splits Inner Join
  accounts On accounts.guid = splits.account_guid
Group By
  accounts.name, accounts.account_type
===

now we order the accounts by type

===
Select
  Sum(splits.value_num),
  accounts.account_type
From
  splits Inner Join
  accounts On accounts.guid = splits.account_guid
Group By
  accounts.account_type
Order By
  accounts.account_type
===

Noooooooooooo! I hear you call, it isn't a balance sheet yet.

Correct, we need to get rid of EXPENSE and INCOME accounts, try this

===
Select
  Sum(splits.value_num),
  accounts.account_type
From
  splits Inner Join
  accounts On accounts.guid = splits.account_guid
Group By
  accounts.account_type
Having
  accounts.account_type Not In ('EXPENSE', 'INCOME')
Order By
  accounts.account_type
===

that, people, is a balance sheet, the rest is presentation.

If you are interested in this sort of stuff, let me know.  If you
already know it, say so, let's share stuff.

If you're a gnc senior and find it irritating speak your word.

My interest is that it takes a lot less SQL than the current reporting
methods to produce a balance sheet.

Next time I'll visit the p&l, income statement, whatever in sql, first I
want this sort of report shot at and possibly killed.

-- 
Wm...