Assuming the first of your tables to be named CHARTS (OK, not the best
name) containing a field to sum named MyField and the latter, linking
table to be called ACCOUNTS, the following SQL should get you the sum
for the group you desire:

WITH RECURSIVE GROUPCONTAINS(SUBCODE, CODE)
AS (SELECT A.CODE, A.SUMMARIZES_IN
FROM ACCOUNTS A
WHERE A.SUMMARIZES_IN = :CODE
UNION ALL
SELECT A.CODE, A.SUMMARIZES_IN
FROM GROUPCONTAINS G
JOIN ACCOUNTS A ON G.SUBCODE = A.SUMMARIZES_IN)

SELECT SUM(C.MyField) FROM GROUPCONTAINS G
JOIN CHARTS C ON G.SUBCODE = C.CODE

Tried on an alpha version of 2.5, but I think WITH RECURSIVE was
introduced in 2.1.

HTH,
Set

Sergio H. Gonzalez wrote:

> Hello, I'm trying o do a very simple accounting software.
> My Chart of Acounts would be like this
>
> 1000
> 1100 (summarizes in 1000) *
> 1200 (summarizes in 1000)
> 1210 (summarizes in 1200) *
> 1220 (summarizes in 1200) *
> 1300 (summarizes in 1000) *
> 1400 (summarizes in 1000)
> 1410 (summarizes in 1400) *
> 1420 (summarizes in 1400)
> 1421 (summarizes in 1420) *
> 1422 (summarizes in 1420) *
>
> Only the accounts with "no childs" can receive an entry, so if I want to total
> amount of the 1000 account, I must to summarize all the entries with the
> accounts marked with "*"
>
> I guess the select I need would be recursive, but I'm trying hard to figure out
> how to do it with no luck
>
> I have two fields in my accounts table like this
>
> code summarizes_in
> ----------------------
> 1000
> 1100 1000
> 1200 1000
> 1210 1200
> 1220 1200
> 1300 1000
> etc...
>
> Can someone give a clue about how to do it using SQL?
> Just in case, I'm using FB 2.1
>
> Thanks!
>
> s