Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U977y-0007uJ-Nd for pgsql-sql@arkaria.postgresql.org; Sat, 23 Feb 2013 04:54:27 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1U977x-0007NY-Vq for pgsql-sql@arkaria.postgresql.org; Sat, 23 Feb 2013 04:54:26 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U977v-0007NL-Ab for pgsql-sql@postgresql.org; Sat, 23 Feb 2013 04:54:23 +0000 Received: from co9ehsobe003.messaging.microsoft.com ([207.46.163.26] helo=co9outboundpool.messaging.microsoft.com) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U977o-0006WL-O7 for pgsql-sql@postgresql.org; Sat, 23 Feb 2013 04:54:21 +0000 Received: from mail164-co9-R.bigfish.com (10.236.132.225) by CO9EHSOBE019.bigfish.com (10.236.130.82) with Microsoft SMTP Server id 14.1.225.23; Sat, 23 Feb 2013 04:54:13 +0000 Received: from mail164-co9 (localhost [127.0.0.1]) by mail164-co9-R.bigfish.com (Postfix) with ESMTP id 90C322201A7; Sat, 23 Feb 2013 04:54:13 +0000 (UTC) X-Forefront-Antispam-Report: CIP:157.56.240.245; KIP:(null); UIP:(null); IPV:NLI; H:BL2PRD0210HT004.namprd02.prod.outlook.com; RD:none; EFVD:NLI X-SpamScore: 0 X-BigFish: PS0(zzc85dhzz1f42h1d77h1ee6h1de0h1202h1e76h1d1ah1d2ahz8dhz8275bh18c673hz2fh2a8h668h839hd25he5bhf0ah1288h12a5h12bdh137ah1441h1504h1537h153bh162dh1631h1758h18e1h1946h19b5h1155h) Received-SPF: pass (mail164-co9: domain of uga.edu designates 157.56.240.245 as permitted sender) client-ip=157.56.240.245; envelope-from=nuse@uga.edu; helo=BL2PRD0210HT004.namprd02.prod.outlook.com ; .outlook.com ; Received: from mail164-co9 (localhost.localdomain [127.0.0.1]) by mail164-co9 (MessageSwitch) id 1361595249384202_14832; Sat, 23 Feb 2013 04:54:09 +0000 (UTC) Received: from CO9EHSMHS001.bigfish.com (unknown [10.236.132.254]) by mail164-co9.bigfish.com (Postfix) with ESMTP id 51F52420083; Sat, 23 Feb 2013 04:54:09 +0000 (UTC) Received: from BL2PRD0210HT004.namprd02.prod.outlook.com (157.56.240.245) by CO9EHSMHS001.bigfish.com (10.236.130.11) with Microsoft SMTP Server (TLS) id 14.1.225.23; Sat, 23 Feb 2013 04:54:08 +0000 Received: from BL2PRD0210MB386.namprd02.prod.outlook.com ([169.254.5.128]) by BL2PRD0210HT004.namprd02.prod.outlook.com ([10.255.106.167]) with mapi id 14.16.0263.000; Sat, 23 Feb 2013 04:54:07 +0000 From: Bryan L Nuse To: Don Parris CC: "pgsql-sql@postgresql.org" Subject: Re: Summing & Grouping in a Hierarchical Structure Thread-Topic: [SQL] Summing & Grouping in a Hierarchical Structure Thread-Index: AQHOCyAJwSsJ7FlVmkKBmoOj7F52WZiFn+UAgAD/jACAACvkAIAAIgMA Date: Sat, 23 Feb 2013 04:54:05 +0000 Message-ID: <8A586FDE-CC0A-4F26-AD19-1F89C2D9349E@uga.edu> References: In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [10.255.106.132] Content-Type: multipart/alternative; boundary="_000_8A586FDECC0A4F26AD191F89C2D9349Eugaedu_" MIME-Version: 1.0 X-OriginatorOrg: uga.edu X-Pg-Spam-Score: -4.2 (----) 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 --_000_8A586FDECC0A4F26AD191F89C2D9349Eugaedu_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable This works fine: test_ltree=3D> SELECT path, trans_amt FROM testcat; path | trans_amt -----------------------------------------+----------- TOP.Transportation.Auto.Fuel | 50.00 TOP.Transportation.Auto.Maintenance | 30.00 TOP.Transportation.Auto.Fuel | 25.00 TOP.Transportation.Bicycle.Gear | 40.00 TOP.Transportation.Bicycle.Gear | 20.00 TOP.Transportation.Fares.Bus | 10.00 TOP.Transportation.Fares.Train | 5.00 TOP.Groceries.Food.Beverages | 30.00 TOP.Groceries.Food.Fruit_Veggies | 40.00 TOP.Groceries.Food.Meat_Fish | 80.00 TOP.Groceries.Food.Grains_Cereals | 30.00 TOP.Groceries.Beverages.Alcohol.Beer | 25.00 TOP.Groceries.Beverages.Alcohol.Spirits | 10.00 TOP.Groceries.Beverages.Alcohol.Wine | 50.00 TOP.Groceries.Beverages.Juice | 45.00 TOP.Groceries.Beverages.Other | 15.00 (16 rows) So if I want to see: TOP.Groceries | 240.00 TOP.Transportation | 180.00 Hello Don, Perhaps I am missing something about what your constraints are, or what you= 're trying to achieve, but is there any reason you could not use a series o= f joined tables indicating parent-child relationships? The following examp= le follows that in your previous posts. Note that this approach (as given)= will not work if branches stemming from the same node are different length= s. That is, if you have costs associated with "Transportation.Bicycle.Gear= ", you could not also have a category "Transportation.Bicycle.Gear.Chain_ri= ng". (To add the latter category, you'd have to put costs from the former = under something like "Transportation.Bicycle.Gear.General" -- or modify the= approach.) However, lengthening the "Alcohol" branches, e.g., by tacking = on a level5 table would be easy. Notice that level3 and level4 are not tru= e look-up tables, since they may contain duplicate cat values. If I'm off base, by all means specify just how. Regards, Bryan -------------------------------------------------- CREATE TABLE level1 ( cat text PRIMARY KEY ); CREATE TABLE level2 ( cat text PRIMARY KEY, parent text REFERENCES level1(cat) ); CREATE TABLE level3 ( cat text, parent text REFERENCES level2(cat), cost numeric(6,2) ); CREATE TABLE level4 ( cat text, parent text, cost numeric(6,2) ); INSERT INTO level1 VALUES ('Transportation'), ('Groceries'); INSERT INTO level2 VALUES ('Auto', 'Transportation'), ('Bicycle', 'Transportation'), ('Fares', 'Transportation'), ('Food', 'Groceries'), ('Beverages', 'Groceries'); INSERT INTO level3 VALUES ('Fuel', 'Auto', 50.00), ('Maintenance', 'Auto', 30.00), ('Fuel', 'Auto', 25.00), ('Gear', 'Bicycle', 40.00), ('Gear', 'Bicycle', 20.00), ('Bus', 'Fares', 10.00), ('Train', 'Fares', 5.00), ('Beverages', 'Food', 30.00), ('Fruit_Veg', 'Food', 40.00), ('Meat_Fish', 'Food', 80.00), ('Grains_Cereals', 'Food', 30.00), ('Alcohol', 'Beverages', NULL), ('Juice', 'Beverages', 45.00), ('Other', 'Beverages', 15.00); INSERT INTO level4 VALUES ('Beer', 'Alcohol', 25.00), ('Spirits', 'Alcohol', 10.00), ('Wine', 'Alcohol', 50.00); CREATE VIEW all_cats AS ( SELECT a.cat AS level4, b.cat AS level3, c.cat AS level2, d.cat AS level1, CASE WHEN a.cost IS NULL THEN 0 WHEN a.cost IS NOT NULL THEN a.cost END + CASE WHEN b.cost IS NULL THEN 0 WHEN b.cost IS NOT NULL THEN b.cost END AS cost FROM level4 a FULL JOIN level3 b ON (a.parent =3D b.cat) FULL JOIN level2 c ON (b.parent =3D c.cat) FULL JOIN level1 d ON (c.parent =3D d.cat) ORDER BY level1, level2, level3, level4 ); SELECT * FROM all_cats; level4 | level3 | level2 | level1 | cost ---------+----------------+-----------+----------------+------- Beer | Alcohol | Beverages | Groceries | 25.00 Spirits | Alcohol | Beverages | Groceries | 10.00 Wine | Alcohol | Beverages | Groceries | 50.00 | Juice | Beverages | Groceries | 45.00 | Other | Beverages | Groceries | 15.00 | Beverages | Food | Groceries | 30.00 | Fruit_Veg | Food | Groceries | 40.00 | Grains_Cereals | Food | Groceries | 30.00 | Meat_Fish | Food | Groceries | 80.00 | Fuel | Auto | Transportation | 50.00 | Fuel | Auto | Transportation | 25.00 | Maintenance | Auto | Transportation | 30.00 | Gear | Bicycle | Transportation | 20.00 | Gear | Bicycle | Transportation | 40.00 | Bus | Fares | Transportation | 10.00 | Train | Fares | Transportation | 5.00 (16 rows) SELECT level1, count(cost) AS num_branches, sum(cost) AS total_cost FROM all_cats GROUP BY level1 ORDER BY level1; level1 | num_branches | total_cost ----------------+--------------+------------ Groceries | 9 | 325.00 Transportation | 7 | 180.00 (2 rows) --_000_8A586FDECC0A4F26AD191F89C2D9349Eugaedu_ Content-Type: text/html; charset="iso-8859-1" Content-ID: Content-Transfer-Encoding: quoted-printable


This works fine:
test_ltree=3D> SELECT path, trans_amt FROM testcat;
            &nb= sp;     path       &= nbsp;           | trans_a= mt
-----------------------------------------+-----------
 TOP.Transportation.Auto.Fuel       = ;     |     50.00
 TOP.Transportation.Auto.Maintenance     | &n= bsp;   30.00
 TOP.Transportation.Auto.Fuel       = ;     |     25.00
 TOP.Transportation.Bicycle.Gear      &n= bsp;  |     40.00
 TOP.Transportation.Bicycle.Gear      &n= bsp;  |     20.00
 TOP.Transportation.Fares.Bus       = ;     |     10.00
 TOP.Transportation.Fares.Train      &nb= sp;   |      5.00
 TOP.Groceries.Food.Beverages       = ;     |     30.00
 TOP.Groceries.Food.Fruit_Veggies      &= nbsp; |     40.00
 TOP.Groceries.Food.Meat_Fish       = ;     |     80.00
 TOP.Groceries.Food.Grains_Cereals      = |     30.00
 TOP.Groceries.Beverages.Alcohol.Beer    |  &= nbsp;  25.00
 TOP.Groceries.Beverages.Alcohol.Spirits |     10.= 00
 TOP.Groceries.Beverages.Alcohol.Wine    |  &= nbsp;  50.00
 TOP.Groceries.Beverages.Juice      &nbs= p;    |     45.00
 TOP.Groceries.Beverages.Other      &nbs= p;    |     15.00
(16 rows)


So if I want to see:
TOP.Groceries        | 240.00
TOP.Transportation | 180.00



Hello Don,

Perhaps I am missing something about what your constraints are, or wha= t you're trying to achieve, but is there any reason you could not use a ser= ies of joined tables indicating parent-child relationships?  The follo= wing example follows that in your previous posts.  Note that this approach (as given) will not work if branches = stemming from the same node are different lengths.  That is, if you ha= ve costs associated with "Transportation.Bicycle.Gear", you could= not also have a category "Transportation.Bicycle.Gear.Chain_ring"= ;.  (To add the latter category, you'd have to put costs from the former= under something like "Transportation.Bicycle.Gear.General" -- or= modify the approach.)  However, lengthening the "Alcohol" b= ranches, e.g., by tacking on a level5 table would= be easy.  Notice that level3 and level4 are not true look-up tables, since they may contain duplicate= cat values.

If I'm off base, by all means specify just how.

Regards,
Bryan

--------------------------------------------------

CREATE TABLE level1 = (
  cat   te= xt  PRIMARY KEY
);

CREATE TABLE level2 = (
   cat &nb= sp; text   PRIMARY KEY,
   parent =   text   REFERENCES level1(cat)
);

CREATE TABLE level3 = (
   cat &nb= sp; text,
   parent =   text   REFERENCES level2(cat),
   cost &n= bsp; numeric(6,2)
);

CREATE TABLE level4 = (
   cat &nb= sp; text,
   parent =   text,
   cost &n= bsp; numeric(6,2)
);


INSERT INTO level1
  VALUES ('Tran= sportation'),
     =    ('Groceries');

INSERT INTO level2
  VALUES ('Auto= ', 'Transportation'),
     =    ('Bicycle', 'Transportation'),
     =    ('Fares', 'Transportation'),
     =    ('Food', 'Groceries'),
     =    ('Beverages', 'Groceries');

INSERT INTO level3
  VALUES ('Fuel= ', 'Auto', 50.00),
     =    ('Maintenance', 'Auto', 30.00),
     =    ('Fuel', 'Auto', 25.00), 
     =    ('Gear', 'Bicycle', 40.00),
     =    ('Gear', 'Bicycle', 20.00),
     =    ('Bus', 'Fares', 10.00),
     =    ('Train', 'Fares', 5.00),
     =    ('Beverages', 'Food', 30.00),
     =    ('Fruit_Veg', 'Food', 40.00),
     =    ('Meat_Fish', 'Food', 80.00),
     =    ('Grains_Cereals', 'Food', 30.00), 
     =    ('Alcohol', 'Beverages', NULL),
     =    ('Juice', 'Beverages', 45.00), 
     =    ('Other', 'Beverages', 15.00); 

INSE= RT INTO level4
  VALUES ('Beer= ', 'Alcohol', 25.00),
     =    ('Spirits', 'Alcohol', 10.00),
     =    ('Wine', 'Alcohol', 50.00);


CREATE VIEW all_cats= AS (
SELECT a.cat AS leve= l4,
     =  b.cat AS level3,
     =  c.cat AS level2,
     =  d.cat AS level1,
     =  CASE WHEN a.cost IS NULL THEN 0
     =       WHEN a.cost IS NOT NULL THEN a.cost
     =            END
     =  + CASE WHEN b.cost IS NULL THEN 0
     =         WHEN b.cost IS NOT NULL THEN b.cost
     =    END AS cost
  FROM level4 a=
    FULL J= OIN
    level3= b
    ON (a.= parent =3D b.cat)
     = FULL JOIN
     = level2 c
     = ON (b.parent =3D c.cat)
     =   FULL JOIN
     =   level1 d
     =   ON (c.parent =3D d.cat)
  ORDER BY leve= l1, level2, level3, level4
);



SELECT * FROM all_ca= ts;

     = level4  |     level3     |  level2   | &= nbsp;   level1     | cost  
&nbs= p;   -----= ----+----------------+-----------+----------------+-------<= /font>
&nbs= p;   &nb= sp;Beer    | Alcohol        | Beverages | Gro= ceries      | 25.00
&nbs= p;   &nb= sp;Spirits | Alcohol        | Beverages | Groceries &nb= sp;    | 10.00
&nbs= p;   &nb= sp;Wine    | Alcohol        | Beverages | Gro= ceries      | 50.00
&nbs= p;   &nb= sp;        | Juice          | = Beverages | Groceries      | 45.00
&nbs= p;   &nb= sp;        | Other          | = Beverages | Groceries      | 15.00
&nbs= p;   &nb= sp;        | Beverages      | Food  = ;    | Groceries      | 30.00
&nbs= p;   &nb= sp;        | Fruit_Veg      | Food  = ;    | Groceries      | 40.00
&nbs= p;   &nb= sp;        | Grains_Cereals | Food      = | Groceries      | 30.00
&nbs= p;   &nb= sp;        | Meat_Fish      | Food  = ;    | Groceries      | 80.00
    <= /font>&nb= sp;        | Fuel          = | Auto      | Transportation | 50.00
     =        | Fuel          = | Auto      | Transportation | 25.00
     =         | Maintenance    | Auto   =    | Transportation | 30.00
&nbs= p;   &nb= sp;        | Gear           | = Bicycle   | Transportation | 20.00
&nbs= p;   &nb= sp;        | Gear           | = Bicycle   | Transportation | 40.00
&nbs= p;   &nb= sp;        | Bus           &nb= sp;| Fares     | Transportation | 10.00
&nbs= p;   &nb= sp;        | Train          | = Fares     | Transportation |  5.00
&nbs= p;   (16= rows)




SELECT level1,
     =  count(cost) AS num_branches,
     =  sum(cost) AS total_cost 
  FROM all_cats=
  GROUP BY leve= l1
  ORDER BY leve= l1;

&nbs= p;   &nb= sp;    level1     | num_branches | total_cost 
&nbs= p;   ---= -------------+--------------+------------
&nbs= p;   &nb= sp;Groceries      |            = ;9 |     325.00
&nbs= p;   &nb= sp;Transportation |            7 |   &nb= sp; 180.00
&nbs= p;   (2 = rows)

--_000_8A586FDECC0A4F26AD191F89C2D9349Eugaedu_--