Archives of the TeradataForum

Message Posted: Thu, 14 Dec 2006 @ 16:15:54 GMT

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

I finally got my answer myself.

Please ignore my post.

     select id
                 ,sum(case  when grp=1
                                  then y
                                  else 0 end) as grp_1_txn_count
              ,sum(case  when grp=2
                                  then y
                                  else 0 end) as grp_2_txn_count
              ,sum(case  when grp=3
                                  then y
                                  else 0 end) as grp_3_txn_count
     from
     (
     Select id
             ,grp
             ,trxn_id
             ,count(*) as y
             from
     (select id
                ,grp
                ,trxn_id
                ,count(*) as x
     from
     (select id
             ,(case    when  dept in ('040','041')
                      then 1
                     when  dept in ('050','051')
                      then 2
                     when  dept in ('060')
                      then 3
                      else 0 end
                 ) as grp
             ,trxn_id
             from txn_detail) xyz
     group by 1,2,3)pqr
     group by 1,2,3
     ) ijk
     group by 1

Thanks

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