SQL: a consolidated balance sheet

[Wm]

Sat, 10 Jan 2015 14:25:44 <YUcT4QIoZTsUFwYE at tarrcity.demon.co.uk>  Wm 
<wm+gnc at tarrcity.demon.co.uk>

>Sun, 28 Dec 2014 10:30:25 <rstfmEQBv9nUFwkM at tarrcity.demon.co.uk>  Wm
><wm+gnc at tarrcity.demon.co.uk>
>
>[ff to self, ongoing SQL tutorial of sorts]
>
>>this is a gnucash balance sheet that should work with any SQL backend
>
>>===
>>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.
>
>So far my SQL examples haven't offered anything you can't already get
>from gnc so, you may ask yourself, why bother ?
>
>Well, to pique your interest here is something you can't do with gnc, a
>consolidated balance sheet.  I've always thought this should work and
>actually got around to trying it out this morning.  It fell together by
>itself.
>
>===
>ATTACH DATABASE 'A.sqlite' AS 'A';
>ATTACH DATABASE 'B.sqlite' AS 'B';
>
>SELECT Sum(A.splits.value_num) V
>        ,A.accounts.account_type T
>        ,A.accounts.NAME N
>FROM A.splits
>INNER JOIN A.accounts ON A.accounts.guid = A.splits.account_guid
>GROUP BY A.accounts.account_type
>HAVING A.accounts.account_type NOT IN ('EXPENSE', 'INCOME')
>
>UNION ALL
>
>SELECT Sum(B.splits.value_num) V
>        ,B.accounts.account_type T
>        ,B.accounts.NAME N
>FROM B.splits
>INNER JOIN B.accounts ON B.accounts.guid = B.splits.account_guid
>GROUP BY B.accounts.account_type
>HAVING B.accounts.account_type NOT IN ('EXPENSE', 'INCOME')
>
>ORDER BY T, N;
>===

===
ATTACH DATABASE 'A.sqlite' AS 'bookA';

ATTACH DATABASE 'B.sqlite' AS 'bookB';

SELECT 'main' AS book   ,* FROM CoA
UNION
SELECT 'bookA'          ,* FROM bookA.CoA
UNION
SELECT 'bookB'          ,* FROM bookB.CoA
===

works well but I can't quite make my mind up if the consolidated CoA 
should actually exist in the top level db or be virtual.  Thoughts 
anyone?

-- 
Wm...