Re: Stuck Up In My Category Tree

On Wed, Aug 17, 2011 at 16:20, Don Parris <parrisdc(at)gmail(dot)com> wrote:

> On Wed, Aug 17, 2011 at 05:24, Chetan Suttraway <
> chetan(dot)suttraway(at)enterprisedb(dot)com> wrote:
>
>>
>> Could you please post a self contained test case here?
>>
>> Regards,
>> Chetan
>>
>>
>> Hi Chetan,
>
> Thanks. I will post something first chance I get - could be tomorrow
> morning though.
>
> I've not made much progress to date, so still need the help. :-(
>
>
> Ok, realized I have a subset of test data with me... The following creates
the 2 tables required for my desired query.

CREATE TABLE category(
category_id serial NOT NULL,
category_name varchar(60),
parent_id int,
lineage varchar(12),
deep int
);

CREATE TABLE transdetails(
transdetails_id serial NOT NULL,
transaction_id int, -- refers to transaction table which is not at all part
of the query result I want
category_id int,
transdetails_item varchar(50),
transdetails_amount numeric(7, 2)
);

The data sets below include enough data to give one a good idea of the
working data. Transactions are categorized at the lowest possible level.
Some are categorized at the root level, while others are categorized at
levels 2 or 3.

Thus summing transactions by category is actually a very simple task, simply
matching:
SELECT categoy_name, sum(transdetails_amount)
FROM category, transdetails
WHERE category.category_id = transdetails

But try showing these details to someone who hates numbers. I want to
summarize, so that I only see the top-level category, regardless of how
deeply a given transaction is categorized... something along the lines of:
SHOW ME only the top categories and the sums of all their subcategories.

[The subset of TABLE category data]
"category_id","category_name","parent_id","lineage","deep"
"category_id","category_name","parent_id","lineage","deep"
1,"Income",,"1",1
2,"Tax",,"2",1
3,"Groceries",,"3",1
4,"Entertainment",,"4",1
5,"Clothing",,"5",1
6,"Gross Salary",1,"1-6",2
7,"Federal Tax",2,"2-7",2
8,"Social Security",2,"2-8",2
9,"Medicare",2,"2-9",2
10,"State",2,"2-10",2
11,"Sales Tax",2,"2-11",2
12,"General Groceries",3,"3-12",2
13,"Food",3,"3-13",2
14,"Grains & Cereals",13,"3-13-14",3
15,"Fruits & Veggies",13,"3-13-15",3
16,"Fish & Meats",13,"3-13-16",3
17,"Snacks & Beverages",13,"3-13-17",
18,"Dairy",13,"3-13-18",3
19,"Beverages (alcohol)",3,"3-19",2
20,"Books",4,"4-20",2
21,"Music",4,"4-21",2
22,"Dining",4,"4-22",2
23,"Discounts",,"23",1
24,"Credit Card",,"24",1
25,"Insurance",,"25-26",1
26,"Car",25,"25-26",2
27,"Medical",25,"25-27",2

[The subset of TABLE transdetails data]
"transdetails_id","transaction_id","category_id","transdetails_item","transdetails_amount"
1,1,22,"Lunch",-6.92
2,2,22,"Dinner",-45.43
3,3,12,"Groceries",-24.04
4,4,12,"Groceries",-26.02
5,5,5,"Baby Clothes",-14.99
6,5,5,"Baby Clothes",-14.99
7,5,11,"Sales Tax (7.25%)",-2.17
8,6,16,"Chicken Drumsticks",-8.56
9,6,23,"SuperMarket Discount",3.1
10,6,11,"Sales Tax (2.00%)",-0.11
11,7,17,"Vanilla Wafers",-4.19
12,7,72,"Brown Sugar",-1.77
13,7,23,"SuperMarket Discount",0.3
14,7,15,"Pineapple",-1.49
15,7,15,"Yellow Bananas",-2.26
16,7,72,"Whipped Cream",-1.69
17,7,18,"2% Milk",-2.69
18,7,11,"Sales Tax (2.00%)",-0.28
19,8,20,"Grep Pocket Reference",-8.65
20,9,24,"Credit Card Payment",-50.00
21,10,6,"Gross Salary",1200.00
22,10,7,"Federal Income Tax",75.00
23,10,8,"Social Security",15.00
24,10,9,"Medicare",10.00
25,10,10,"State Income Tax",30.00
26,10,27,"Insurance",70.00
[End of DATA]

My actual category table has @ 135 rows and could grow a bit more, but is
mostly stable. I don't really foresee a need to go deeper, but that is a
possibility. I do seem to encounter the need to add a new category every so
often. I can also see where I might need to manage this issue in other
areas.

Most of the tutorials I have seen show techniques for creating web crumbs
for forums, etc. Their focus is on showing a root, plus all its branch
categories. Maybe I've overlooked something that explains how to achieve
this:
SHOW ME all top_categories AND count(threads_in_all_subcategories), which
would be similar. Thus a person could see how many total threads were in a
given forum, with its sub-forums and sub-sub-forums. And maybe there are
threads at each level.

I have a (fairly dated) book on database design that effectively doesn't
even address the issue at all, and "Beginning Databases with PostgreSQL"
(Matthew/Stones) only barely skims the issue.

Regards,
Don
--
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