Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U8jJ7-0004gW-54 for pgsql-sql@arkaria.postgresql.org; Fri, 22 Feb 2013 03:28:21 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1U8jJ6-0000x8-Fa for pgsql-sql@arkaria.postgresql.org; Fri, 22 Feb 2013 03:28:20 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U8jJ5-0000x2-7B for pgsql-sql@postgresql.org; Fri, 22 Feb 2013 03:28:19 +0000 Received: from co03.mbox.net ([165.212.64.33] helo=cmsout01.mbox.net) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U8jIy-0004gw-PG for pgsql-sql@postgresql.org; Fri, 22 Feb 2013 03:28:18 +0000 Received: from co03.mbox.net (localhost [127.0.0.1]) by cmsout01.mbox.net (Postfix) with ESMTP id 3ZBygG2vsWzvRrs; Fri, 22 Feb 2013 03:28:10 +0000 (UTC) X-USANET-Received: from co03.mbox.net [127.0.0.1] by co03.mbox.net via mtad (C8.MAIN.3.82G) with ESMTP id 525RBVDCg0896M03; Fri, 22 Feb 2013 03:28:05 -0000 X-USANET-Routed: 3 gwsout-vs Q:bmvirus X-USANET-GWS2-Tagid: UNKN Received: from ca31.cms.usa.net [165.212.11.131] by co03.mbox.net via smtad (C8.MAIN.3.89G) with ESMTP id XID456RBVDCg1245X03; Fri, 22 Feb 2013 03:28:05 -0000 X-USANET-Source: 165.212.11.131 OUT gataric@usa.net ca31.cms.usa.net X-USANET-MsgId: XID456RBVDCg1245X03 Received: from AlexPC [98.215.52.64] by ca31.cms.usa.net (ESMTPSA/gataric@usa.net) via mtad (C8.MAIN.3.82G) with ESMTPSA id 737RBVDCF5632M31; Fri, 22 Feb 2013 03:28:05 -0000 X-USANET-Auth: 98.215.52.64 AUTH gataric@usa.net AlexPC From: "Alexander Gataric" To: "'Don Parris'" , References: <001601ce0b35$3a395090$aeabf1b0$@net> In-Reply-To: Subject: Re: Summing & Grouping in a Hierarchical Structure Date: Thu, 21 Feb 2013 21:28:02 -0600 Message-ID: <007201ce10ac$9f655280$de2ff780$@net> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0073_01CE107A.54CAE280" X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: Ac4QH72FygZ49Oo7RPumTSp0RLijlQAjAZyw Content-Language: en-us Z-USANET-MsgId: XID737RBVDCF5632X31 X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org This is a multi-part message in MIME format. ------=_NextPart_000_0073_01CE107A.54CAE280 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit I would use the recursive CTE to gather the hierarchical portion of the data you need and then join that CTE to another table or CTE with the other data you need. I had a situation like this at my job were organization info was in a hierarchal table and I needed to join it to two other tables. I created a CTE with the combined data from the non-hierarchical tables and left joined it to the recursive CTE. If you're having trouble with this, I suggest looking into CTEs and the different types of joins. From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Don Parris Sent: Thursday, February 21, 2013 4:38 AM To: pgsql-sql@postgresql.org Subject: Re: [SQL] 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 wrote: 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-owner@postgresql.org [mailto:pgsql-sql-owner@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=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/ GPG Key ID: F5E179BE -- D.C. Parris, FMP, Linux+, ESL Certificate Minister, Security/FM Coordinator, Free Software Advocate http://dcparris.net/ GPG Key ID: F5E179BE ------=_NextPart_000_0073_01CE107A.54CAE280 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

I would use the recursive CTE to gather the hierarchical portion of = the data you need and then join that CTE to another table or CTE with = the other data you need. I had a situation like this at my job were = organization info was in a hierarchal table and I needed to join it to = two other tables. I created a CTE with the combined data from the = non-hierarchical tables and left joined it to the recursive = CTE.

 

If you’re having trouble with this, I suggest looking into CTEs = and the different types of joins.

 

 

 

From:= = pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] = On Behalf Of Don Parris
Sent: Thursday, February 21, = 2013 4:38 AM
To: pgsql-sql@postgresql.org
Subject: = Re: [SQL] 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@usa.net> = wrote:

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-owner@postgresql.org [mailto:pgsql-sql-owner@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

 <= /o:p>

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-ZN= YwEkR2jdLKeL8nfGc+-TLLSW=3DRmo1VkbA@mail.gmail.com

 <= /o:p>

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.

 <= /o:p>

Regards,

Don

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

GPG Key ID: = F5E179BE




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

GPG Key ID: = F5E179BE

<= /html> ------=_NextPart_000_0073_01CE107A.54CAE280--