Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 14 Dec 2006 @ 16:14:56 GMT


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


Subj:   Avoiding distinct in grouping
 
From:   Anomy Anom

<-- Anonymously Posted: Thursday, December 14, 2006 10:31 -->

     /*CREATE SET VOLATILE TABLE txn_detail ,NO FALLBACK ,
                  CHECKSUM = DEFAULT,
                  LOG
                  (
                   ID DECIMAL(4,0),
                   dept CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
                   TRXN_ID DECIMAL(4,0),
                   x INTEGER)
             PRIMARY INDEX (ID ,TRXN_ID ,dept )
             ON COMMIT PRESERVE ROWS;

             Insert into txn_detail (ID,dept,TRXN_ID,x) values (1111,'040',9000,1);
             Insert into txn_detail (ID,dept,TRXN_ID,x) values (1111,'041',9000,1);
             Insert into txn_detail (ID,dept,TRXN_ID,x) values (1111,'050',8000,1);
             Insert into txn_detail (ID,dept,TRXN_ID,x) values (1111,'051',7000,1);
             Insert into txn_detail (ID,dept,TRXN_ID,x) values (1111,'060',6000,1);
             Insert into txn_detail (ID,dept,TRXN_ID,x) values (1111,'000',0000,4);



     select * from txn_detail;
     */


     When dept in ('040','041') I call it group_1
     When dept in ('050','051') I call it group_2
     When dept in ('060') I call it group_3

I need to get the count of the number of distinct TRXN_ID grouping by ID. The trick is I don't want to use the Distinct as I have millions of records and distinct takes a lot of time and resources to execute

The output should look like:

     ID     group_1_txn_count   group_2_txn_count   group_3_txn_count
     ----------------------------------------------------------------
     1111                   1                   2                   1

Any help with this.

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