Re: Summing & Grouping in a Hierarchical Structure

I decided to try out ltree, and have made some progress with it.  If I understand correctly how to use it, I simply insert the 'path' column into my table, using ltree as the data type.  That eliminates the need for a category table, if I understand correctly.  I just need to ensure the category path is correct for each line item in the transaction details table.

However, I have difficulty figuring out how to sum the amounts as I would like:
test_ltree=> SELECT path, SUM(trans_amt) FROM testcat WHERE path ~ 'TOP.*{2}' GROUP BY path;
 path | sum
------+-----
(0 rows)


This works fine:
test_ltree=> SELECT path, trans_amt FROM testcat;
                  path                   | trans_amt
-----------------------------------------+-----------
 TOP.Transportation.Auto.Fuel            |     50.00
 TOP.Transportation.Auto.Maintenance     |     30.00
 TOP.Transportation.Auto.Fuel            |     25.00
 TOP.Transportation.Bicycle.Gear         |     40.00
 TOP.Transportation.Bicycle.Gear         |     20.00
 TOP.Transportation.Fares.Bus            |     10.00
 TOP.Transportation.Fares.Train          |      5.00
 TOP.Groceries.Food.Beverages            |     30.00
 TOP.Groceries.Food.Fruit_Veggies        |     40.00
 TOP.Groceries.Food.Meat_Fish            |     80.00
 TOP.Groceries.Food.Grains_Cereals       |     30.00
 TOP.Groceries.Beverages.Alcohol.Beer    |     25.00
 TOP.Groceries.Beverages.Alcohol.Spirits |     10.00
 TOP.Groceries.Beverages.Alcohol.Wine    |     50.00
 TOP.Groceries.Beverages.Juice           |     45.00
 TOP.Groceries.Beverages.Other           |     15.00
(16 rows)