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: (view raw, whole thread or download thread mbox)
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
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]
6,"Gross Salary",1,"1-6",2
7,"Federal Tax",2,"2-7",2
8,"Social Security",2,"2-8",2
11,"Sales Tax",2,"2-11",2
12,"General Groceries",3,"3-12",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",
19,"Beverages (alcohol)",3,"3-19",2
24,"Credit Card",,"24",1

[The subset of TABLE transdetails data]
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
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
25,10,10,"State Income Tax",30.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

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
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.

D.C. Parris, FMP, LEED AP O+M, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate  |
GPG Key ID: F5E179BE

In response to


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-2017 The PostgreSQL Global Development Group