Home » SQL & PL/SQL » SQL & PL/SQL » How to Aggregate records in the same Table
How to Aggregate records in the same Table [message #260392] Sun, 19 August 2007 08:20 Go to next message
bbaz
Messages: 138
Registered: April 2007
Senior Member
Hi all,

I have a TABLE with the following structure:

CREATE TABLE STREAM_DD_TEMP ( 
  LOC_CD                  VARCHAR2 (10)  NOT NULL, 
  STREAM_DD_DT            DATE          NOT NULL, 
  STREAM_DD_ORIG_TYPE_CD  VARCHAR2 (1)  NOT NULL, 
  STREAM_DD_ORIG_CD       VARCHAR2 (10)  NOT NULL, 
  STREAM_DD_DEST_TYPE_CD  VARCHAR2 (1)  NOT NULL, 
  STREAM_DD_DEST_CD       VARCHAR2 (10)  NOT NULL, 
  STREAM_DD_NO            NUMBER (5), 
  STREAM_PROD_TYPE_CD     VARCHAR2 (1)  NOT NULL, 
  PROD_CD                 VARCHAR2 (5)  NOT NULL, 
  PROD_CSMP_TYPE_CD       VARCHAR2 (1), 
  PROD_CSMP_CD            VARCHAR2 (5), 
  PROD_PRODTN_TYPE_CD     VARCHAR2 (1), 
  PROD_PRODTN_CD          VARCHAR2 (5), 
  PROD_INTER_PRODTN_CD    VARCHAR2 (5), 
  STREAM_ORIG_TYPE_CD     VARCHAR2 (1), 
  STREAM_ORIG_CD          VARCHAR2 (10), 
  STREAM_DEST_TYPE_CD     VARCHAR2 (1), 
  STREAM_DEST_CD          VARCHAR2 (10), 
  STREAM_NO               NUMBER (5), 
  STREAM_DD_ACT_QTY_VOL   NUMBER (9,2)  NOT NULL, 
  STREAM_DD_ACT_QTY_WGHT  NUMBER (9,2)  NOT NULL, 
  STREAM_DD_REC_QTY_VOL   NUMBER (9,2)  NOT NULL, 
  STREAM_DD_REC_QTY_WGHT  NUMBER (9,2)  NOT NULL, 
  LAST_UPD_DT             DATE, 
  LAST_USER_CD            VARCHAR2 (30), 
  LAST_PROC_CD            VARCHAR2 (30) ) ;


When I have records with the SAME DATE STREAM_DD_DT, SAME STREAM_DD_ORIG_CD, SAME STREAM_DD_DEST_CD, and SAME PROD_CD, I need to Aggregate those records getting the SUM of their corresponding VOLUME AND WEIGHT, so I will end up with ONLY one Record for a Specific Date with the Same Origin Code, Destination Code and Prod Code.

Example of 2 records to be aggregated, they have same date 8/17/2007, Orig BERTH1, dest D18, prod KJ1-1:
1,8/17/2007,O,BERTH1,T,D18,12345,H,KJ1-1,,,H,KJ1-1,,O,BERTH1,T,D18,54321,5633,8612,4444,5555,8/19/2007 9:41:39 AM,SUPPORT,MVT_SPLIT_INTER
1,8/17/2007,O,BERTH1,T,D18,12345,H,KJ1-1,,,H,KJ1-1,,O,BERTH1,T,D18,54321,9345,3426,4444,5555,8/19/2007 9:41:39 AM,SUPPORT,MVT_SPLIT_INTER



Currently, I am aggregating the records and inserting them into another TABLE (STREAM_DD_AGGREGATE) which has the same structure as the original Table as per the following Insert Statement:

INSERT INTO STREAM_DD_AGGREGATE 
(
  LOC_CD, 
  STREAM_DD_DT, 
  STREAM_DD_ORIG_TYPE_CD, 
  STREAM_DD_ORIG_CD, 
  STREAM_DD_DEST_TYPE_CD, 
  STREAM_DD_DEST_CD, 
  STREAM_PROD_TYPE_CD, 
  PROD_CD,
  STREAM_DD_ACT_QTY_VOL, 
  STREAM_DD_ACT_QTY_WGHT, 
  STREAM_DD_REC_QTY_VOL, 
  STREAM_DD_REC_QTY_WGHT
)
(select 
  LOC_CD, 
  STREAM_DD_DT, 
  STREAM_DD_ORIG_TYPE_CD, 
  STREAM_DD_ORIG_CD, 
  STREAM_DD_DEST_TYPE_CD, 
  STREAM_DD_DEST_CD, 
  STREAM_PROD_TYPE_CD, 
  PROD_CD, 
  SUM(STREAM_DD_ACT_QTY_VOL), 
  SUM(STREAM_DD_ACT_QTY_WGHT), 
  SUM(STREAM_DD_REC_QTY_VOL), 
  SUM(STREAM_DD_REC_QTY_WGHT)
FROM STREAM_DD_TEMP
GROUP by
  LOC_CD, 
  STREAM_DD_DT, 
  STREAM_DD_ORIG_TYPE_CD, 
  STREAM_DD_ORIG_CD, 
  STREAM_DD_DEST_TYPE_CD, 
  STREAM_DD_DEST_CD, 
  STREAM_PROD_TYPE_CD, 
  PROD_CD 
) 



Is there a better way of doing this Task on the SAME ORIGINAL TABLE?

Thanks in advance
Re: How to Aggregate records in the same Table [message #260435 is a reply to message #260392] Sun, 19 August 2007 21:51 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You could INSERT /*+APPEND*/ or CREATE TABLE AS SELECT, which would reduce UNDO I/O. If you use INSERT, make sure you drop all indexes on STREAM_DD_AGGREGATE first.

An index on STREAM_DD_TEMP with all of your GROUP BY columns may eliminate a sort.

Also, if there was only a small number of duplicate rows, it would be faster to replace them in STREAM_DD_TEMP, that way you don't have to rebuild all of the non-duplicated rows.

Ross Leishman
Re: How to Aggregate records in the same Table [message #260495 is a reply to message #260435] Mon, 20 August 2007 02:51 Go to previous message
bbaz
Messages: 138
Registered: April 2007
Senior Member
Thanks Ross,

I appreciate your input.

You mention:
Quote:

Also, if there was only a small number of duplicate rows, it would be faster to replace them in STREAM_DD_TEMP, that way you don't have to rebuild all of the non-duplicated rows.



Can you elaborate on this scenario? I was trying to come up with a way to do the aggregation on the same table and not to Create another Table structure to Store in the Aggregated records.

regards,
Baz
Previous Topic: Inventory Transfer Query
Next Topic: max select on specific date format
Goto Forum:
  


Current Time: Fri Dec 09 19:15:30 CST 2016

Total time taken to generate the page: 0.11501 seconds