Skip site navigation (1) Skip section navigation (2)

Re: Stuck Up In My Category Tree

From: Don Parris <parrisdc(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Stuck Up In My Category Tree
Date: 2011-08-17 23:31:52
Message-ID: CAJ-7yomim=q_-0_foU_r6D-M=vVhLtQv+m1W7zyPAYbe7QVrSA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

Responses

pgsql-novice by date

Next:From: Hans Edwin WinzelerDate: 2011-08-18 15:07:58
Subject: Invalid input syntax for integer
Previous:From: Don ParrisDate: 2011-08-17 20:20:48
Subject: Re: Stuck Up In My Category Tree

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group