Stuck Up In My Category Tree

Hi all,

I created a category table like so:
cat_id(serial) | cat_name(varchar) | parent_id(int) |
lineage(varchar) | deep(int)
1 root_cat_a Null
1 1
2 sub_cat_1 1
1-2 2
3 sub_sub_cat_a 2
1-2-3 3

I use this to categorize transactions, and use the most appropriate
subcategory for any given transation item in a table called trans_details.
I can easily show transaction amounts by sub-category (SELECT cat_name,
sum(amount) FROM category, trans_details WHERE category_cat_id =
trans_details.cat_id):

cat_name | amount
Transportation: Auto: Fuel | $100
Transportation: Auto: Maint | $150
Transportation: Fares: Bus | $40

but what I cannot figure out is how to create a summary where I show
cat_name | amount
Transportation: Auto: | $250

or, what I *really* want:
cat_name | amount
Transportation | $290

Can anyone help me work through this? Frankly, I'm not even sure where to
begin to solve the problem. I have been trying the WITH RECURSIVE feature,
but I do not understand how to apply it in my case. The example query I
have brings up an empty result set and I don't have a clue how I could
modify it to make it work. I'm not even sure it's the best method, but it
seems like a reasonable approach.

I found the below e-mail from a thread on this kind of topic (apparently in
2007, I only copied the relevant text). The author created a very similar
table to mine, but talks more about how to select the child categories, not
the root. I just don't yet have the experience to understand this well
yet. :-)

[begin copied e-mail text]
If the data is static (or if you can get away with running a cron job
every now and then), you can write a recursive pl/pgslq function to
get level information for each node on the tree and assign a specific
"incremental" node_id for each record. Due to the nature of the
recursive function, a node_id is assigned to the children of a
specific node instead of its siblings. You should end up with data as
illustrated below.

id info parent_id level node_id
1 Name1 Null 1 1
2 Name2 1 2 2
3 Name3 2 3 3
4 Name4 3 4 4
5 Name5 4 5 5
6 Name5 1 2 6
7 Name6 6 3 7
8 Name7 1 2 8

Then you can simply retrieve the children of node (N) on level (L)
with a single statement.

SELECT * FROM table WHERE node_id > N AND node_id < (SELECT node_id
FROM table WHERE level = L AND node_id > N ORDER BY node_id LIMIT 1
OFFSET 0);

Refrain from using MIN() as performance suffers unbelievably.
[end copied e-mail text]

Thanks in advance!
--
D.C. Parris, FMP, LEED AP O+M, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
https://www.xing.com/profile/Don_Parris |
http://www.linkedin.com/in/dcparris
GPG Key ID: F5E179BE