At 12:05 AM 19/09/2006, you wrote:

>I have a Firebird 1.5.3 database with a table in it with the structure:
>
>PAR_RID NUMERIC(18,0),
>KEY_RID NUMERIC(18,0),
>LEVEL INTEGER DEFAULT 0,
>QTY INTEGER DEFAULT 0
>
>
>PAR_RID = Parent Record ID
>KEY_RID = Key Record ID
>LEVEL = Level in a tree
>QTY = Quantity
>
>The table is the basis for a "tree" structure.
>
>The LEVEL field is present just on a hunch that I might need it.
>
>My question is this... I have to SUM() all the QTY fields the root
>node, and all of it's children and all children of all their children,
>and their children, and their children, etc. etc. so that each 'node'
>can display only the sum of all it's own descendants and the only node
>that shows a total of every other node including itself, is the root node.
>
>Is there a 'standard'/'well known' way (SQL statment) for doing this?

Not really. SQL isn't by nature tree-like. The usual approach is to
write a selectable stored procedure that takes a node as input and
recurses through the sub-branches (levels) of the node until it hits bottom.

> I will add more fields if necessary. I will take any approach, one
>that requires storing totals based on BEFORE/AFTER UPDATE,
>BEFORE/AFTER INSERT triggers, or an approach that calculates 'live'
>based on user clicking a button.

It's a very bad idea to store totals in any event but *particularly*
when they are to be retrieved recursively. Rather, you should store
a nullable value, and have your rollup procedure accrete values from
the bottom up, aggregating nulls as zero en route. What you accrete
and how you accrete it depends on your business rules, e.g. ignore or
include non-null values in all but leaf nodes, or whatever.

Try googling "SQL", "trees" and "Joe Celko". There are quite a few
papers out there by Celko on the subject, with algorithms that you can adapt.

./heLen