Re: Summing & Grouping in a Hierarchical Structure

Hi Alexander,

I appreciate you taking time to reply to my post. I like the idea of the
WITH RECURSIVE query, but... The two examples in the link you offered are
not so helpful to me. For example, the initial WITH query shown uses a
single table, and I wander how that might apply in my case, where the
relevant information is actually found in two tables, one of them a
recursive table.

The second example, which applies the WITH RECURSIVE clause, is even less
so. I wonder if there is a good tutorial somewhere on this that shows some
other examples? That might help me catch on a little better. I'll search
for that today.

On Thu, Feb 14, 2013 at 11:30 PM, Alexander Gataric <gataric(at)usa(dot)net> wrote:

> I would try a recursive<http://www.postgresql.org/docs/8.4/static/queries-with.html>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-owner(at)postgresql(dot)org [mailto:
> pgsql-sql-owner(at)postgresql(dot)org] *On Behalf Of *Don Parris
> *Sent:* Thursday, February 14, 2013 7:58 PM
> *To:* pgsql-sql(at)postgresql(dot)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+-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 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/ <https://www.xing.com/profile/Don_Parris>****
>
> GPG Key ID: F5E179BE****
>

--
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/
<https://www.xing.com/profile/Don_Parris><http://www.linkedin.com/in/dcparris>
GPG Key ID: F5E179BE