Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 20 Apr 2004 @ 20:36:28 GMT


     
  <Prev Next>  
<<First
<Prev
Next> Last>>  


Subj:   Group By and non-aggregated values
 
From:   Anomy Anom

<-- Anonymously Posted: Tuesday, April 20, 2004 16:13 -->

The following is the scenario of the problem -

     TABLE 1 -

     X Y Z ZZ A B C D E F a b c d
     1 1 1 AB 1 1 1 1 1 1 u v u i
     1 1 1 AC 1 1 2 1 1 2 u v x j
     2 2 2 AB 2 2 2 2 2 2 k k e 3
     2 2 2 DE 2 2 3 2 2 7 k e k e
     3 3 3 BB 1 1 1 1 1 1 p n e f

     WHERE X,Y,Z,ZZ are part of PK
     A, B, C, D, E, F are Integer values
     a, b, c, d are attributes values for the rows.

TABLE 2 -

     X Y Z A B C D E F a b c d

     WHERE X, Y, Z are part of PK and there is no ZZ.
     A, B, C, D, E, F are Integer values
     a, b, c, d are attributes values for the rows.

Data needs to inserted into Table 2 table from Table 1 -

     X Y Z A B C D E F a b c d
     1 1 1 2 2 3 2 2 3 u v u i
     2 2 2 4 4 5 4 4 9 u v x j
     3 3 3 1 1 1 1 1 1 k k e 3

In case there are multiple rows based on ZZ from Table 1, then insert using SUM(A), SUM(B), SUM(D), SUM(E), SUM(F) for those rows from Table 1

But the problem is to implement using SQL where the SQL should be able to SUM up the values for A, B.. ,F column if there are multiple ZZ in it.

The query -

     INSERT INTO Table2
     (
           a,
           b,
           c,
           d,
           A,
           B,
           C,
           D,
           E,
           F,
           X,
           Y,
           Z
     )
     SELECT
           a,
           b,
           c,
           d,
           SUM (A),
           SUM (B),
           SUM (C),
           SUM (D),
           SUM (E),
           SUM (F),
           X,
           Y,
           Z
     FROM Table1
     group by X, Y, Z, ZZ;

does not work because a, b, c are part of non-aggregated values.

The query needs to be executed from a proc and the source table has 10m rows. FOR Cursor processing will go on for ever for such count.

A partial Insert could be done using temp tables, but I am looking for cleaner method.


Thanks



     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016