Archives of the TeradataForum

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

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

<-- 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>>