> From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Don Parris
> Sent: Thursday, February 14, 2013 8: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
withmy 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=Rmo1VkbA@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
detailedlevels 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-levelcategories.
> Anyway, I hope this helps someone, as I have come to appreciate - and I mean really appreciate - the challenge of
workingwith 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
My two cents would be to actually use a different tool for the job of presenting this data. I'd have used a pivot
tablein Microsoft Excel. Not sure what your environment or requirements are but pivot tables are widely used in
business,easy to share, can be formatted, and give the user the ability to drill down and navigate to the data they
wantto see.
I'd set up a query to pull the raw data you need with all of the categories and associated data you need. Then bring
thatdata to Excel to present and summarize it.
Mike
