Re: [SQL] Summing & Grouping in a Hierarchical Structure

[Alexander Gataric]

I would try a recursive
  query to
determine the category structure and aggregate as you go. I had a similar
problem with a hierarchical structure for an organization structure. Another
thing you might try is to create a separate CTE for each category and then
aggregate the individual CTEs.

 

 

From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org]
On Behalf Of Don Parris
Sent: Thursday, February 14, 2013 7:58 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] Summing & Grouping in a Hierarchical Structure

 

Hi all,

I posted to this list some time ago about working with a hierarchical
category structure.   I had great difficulty with my problem and gave up for
a time.  I recently returned to it and resolved a big part of it.  I have
one step left to go, but at least I have solved this part.

Here is the original thread (or one of them):
http://www.postgresql.org/message-id/CAJ-7yonw4_qDCp-ZNYwEkR2jdLKeL8nfGc+-TL
LSW=rmo1v...@mail.gmail.com


 

Here is my recent blog post about how I managed to show my expenses summed
and grouped by a mid-level category:
http://dcparris.net/2013/02/13/hierarchical-categories-rdbms/


Specifically, I wanted to sum and group expenses according to categories,
not just at the bottom tier, but at higher tiers, so as to show more
summarized information.  A CEO primarily wants to know the sum total for all
the business units, yet have the ability to drill down to more detailed
levels if something is unusually high or low.  In my case, I could see the
details, but not the summary.  Well now I can summarize by what I refer to
as the 2nd-level categories.

Anyway, I hope this helps someone, as I have come to appreciate - and I mean
really appreciate - the challenge of working with hierarchical structures in
a 2-dimensional RDBMS.  If anyone sees something I should explain better or
in more depth, please let me know.

 

Regards,

Don

-- 
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate

http://dcparris.net/  

GPG Key ID: F5E179BE

[SQL] Summing & Grouping in a Hierarchical Structure

[Don Parris]

Hi all,

I posted to this list some time ago about working with a hierarchical
category structure.   I had great difficulty with my problem and gave up
for a time.  I recently returned to it and resolved a big part of it.  I
have one step left to go, but at least I have solved this part.

Here is the original thread (or one of them):
http://www.postgresql.org/message-id/CAJ-7yonw4_qDCp-ZNYwEkR2jdLKeL8nfGc+-TLLSW=rmo1v...@mail.gmail.com

Here is my recent blog post about how I managed to show my expenses summed
and grouped by a mid-level category:
http://dcparris.net/2013/02/13/hierarchical-categories-rdbms/


Specifically, I wanted to sum and group expenses according to categories,
not just at the bottom tier, but at higher tiers, so as to show more
summarized information.  A CEO primarily wants to know the sum total for
all the business units, yet have the ability to drill down to more detailed
levels if something is unusually high or low.  In my case, I could see the
details, but not the summary.  Well now I can summarize by what I refer to
as the 2nd-level categories.

Anyway, I hope this helps someone, as I have come to appreciate - and I
mean really appreciate - the challenge of working with hierarchical
structures in a 2-dimensional RDBMS.  If anyone sees something I should
explain better or in more depth, please let me know.

Regards,
Don
-- 
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/

GPG Key ID: F5E179BE