Posted by Steve on 01/19/07 15:40

mouac01@yahoo.com wrote:
> I want to sum up the amt field where the acct or group is A1000. Since
> A1110 belongs to group A1100, and A1100 belongs to group A1000 it
> should pick those amts up as well. The total should be 600. I have
> the sql below but I can't figure how to pick up acct A1110 since it
> should be rolling up to A1000 because of A1100.
>
> select sum(amt) from tbl_bal
> where acct='A1000' or group='A1000';
>
> tbl_bal
> acct group amt
> A1000 A1000 100
> A1100 A1000 200
> A1110 A1100 300

I would agree with othell...@yahoo.com about the design...at least its
not clear.
In SQL Server 2005 this approach might work depending on the tables
design.

WITH Balance(Acct, amt)
AS
(
SELECT [group], amt FROM tbl_bal WHERE acct = 'A1000'
UNION ALL
SELECT G.acct , G.amt FROM tbl_bal G
INNER JOIN Balance B
ON G.[group] = B.acct WHERE G.acct <> 'A1000'
)
SELECT SUM(amt) FROM Balance;

[Back to original message]