Thread: Aggregating over nodes in hierarchical trees

Hello,
I have data in a hierarchical tree structure in one table (tree_tbl) containing the number of students enrolled in a
collegecourse. The college course name is stored as a tree giving the college name, department, and course name, and
perhapsother descendent nodes such as section name. I have another table (node_tbl) that contains the particular nodes
Iam interested in such as the name of the college, department or course. I'd like to join the two tables so I can
aggregatethe total students in all courses containing the node names in the node_tbl. Unfortunately, I've been unable
tosolve this problem and was hoping for help. I had tried using the ltree data type for this purpose, but am open to
othersuggestions if a better data type exists. 

Here is an example of what I'm trying to do:

CREATE TABLE tree_tbl (course LTREE, students INTEGER);
INSERT INTO tree_tbl VALUES ('Arts.English.Shakespeare', 10);
INSERT INTO tree_tbl VALUES ('Arts.Music.Composition', 15);
INSERT INTO tree_tbl VALUES ('Arts.Music.Theory', 11);
INSERT INTO tree_tbl VALUES ('Science.Math.Algebra', 21);
INSERT INTO tree_tbl VALUES ('Science.Biology.IntroBio.SectionA', 20);
INSERT INTO tree_tbl VALUES ('Science.Biology.IntroBio.SectionB', 30);
INSERT INTO tree_tbl VALUES ('Science.Biology.Genetics', 3);

CREATE TABLE node_tbl (node TEXT);
INSERT INTO node_tbl VALUES ('Arts');
INSERT INTO node_tbl VALUES ('English');
INSERT INTO node_tbl VALUES ('Biology');

I would then like to join these two tables so that I can sum the total number of students in each area. Something like
this:

SELECT n.node, sum(students) as students
FROM tree_tbl t, node_tbl n
WHERE t.course ~ '.*' || n.node || '.*'
GROUP BY n.node;

My hope that this query would return this:
node       students
-------   --------
Arts        36
English    10
Biology    53

Unfortunately, this query gives the error:
ERROR:  syntax error at position 0
LINE 3: WHERE t.course ~ '.*' || n.node || '.*'
                         ^

Any help with this problem would be greatly appreciated.

Thanks,
Robert McGehee

"McGehee, Robert" <Robert.McGehee@geodecapital.com> writes:

> SELECT n.node, sum(students) as students
> FROM tree_tbl t, node_tbl n
> WHERE t.course ~ '.*' || n.node || '.*'
> GROUP BY n.node;

I'd write this as

SELECT n.node, sum(students) AS students
FROM tree_tbl t
JOIN node_tbl n ON t.course ~ ('*.' || n.node || '.*')::lquery
GROUP BY n.node;