A query to summarize data in sub-tree?

Question

My data fits a tree form naturally. Therefore, I have a simple SQL table to store the data: {id, parentid, data1, ..., dataN}

I want to be able to "zoom in" on the data and produce a report which summarizes the data found below the current branch. That is, when standing in the root, I want to have the totals of all the data. When I have traveled down a certain branch of the tree, I want to only have the summation of the data found only for that node and its child nodes.

How do I write such a query in SQL?

Thanks in advance!

/John

Answer 1

Since sqlite does not support CONNECT BY, you will not be able to perform this calculation in a single query unless you use nested sets or materialized paths for your data.

Alternatively, do it "the hard way" and traverse your tree recursively, one query for each child node starting at the parent-of-interest.

Also see:

• http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
• http://www.kamfonas.com/id3.html

Answer 2

Vlad's reference on nested sets looks pretty good. If you want something that covers trees and hierarchies in more detail then you can also check out Joe Celko's book.

The "ID, ParentID" adjacency list model is really an "old time" way of looking at hierarchies in a relational database model.