Is this query possible?

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
acctgroupamt
A1000A1000100
A1100A1000200
A1110A1100300I think this violates some of the basic principles of relational model
(not sure which ones). However I would love to see some sort of
solution to this problem though. There is some type of process to break
this into normal table with right groupings.

mouac01@.yahoo.com wrote:

Quote:

Originally Posted by

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
acctgroupamt
A1000A1000100
A1100A1000200
A1110A1100300

|||mouac01@.yahoo.com wrote:

Quote:

Originally Posted by

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
acctgroupamt
A1000A1000100
A1100A1000200
A1110A1100300

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;|||Would need a few more rows to make sure but from what you have given
you need to do a google search on -- recursive SQL -- web is better.
It is not fun or pretty but it is rather common and far better example
exists then I could copy/paste here.