SQL for sum of all "children"

SQL for sum of all "children"

Doing a table for budget accounts, my hierarchy is stored in a "path"
column like so:
groceries.produce.vegetables.tomatoes
groceries.produce.vegetables.potatoes
groceries.produce.fruit
groceries.baking.flour
etc
Works great when I need to grab all produce because I can just do a
LIKE 'produce.%'
query and get anything beneath it in the tree.
However, for any given query, I would like to have a "total" column to sum
up prices of all records "beneath".
The only way I can think of is to do a pattern match on a GROUP BY, but I
have a hunch I may be going down the wrong path.
Any suggestions would be much appreciated.