Archives of the TeradataForum

Message Posted: Thu, 14 Dec 2006 @ 21:46:50 GMT

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

Nizam Syed wrote:

You can get rid of some of those Derived Tables:

     SELECT
        id
        ,COUNT(CASE  WHEN grp=1 THEN 1 END) AS grp_1_txn_count
        ,COUNT(CASE  WHEN grp=2 THEN 1 END) AS grp_2_txn_count
        ,COUNT(CASE  WHEN grp=3 THEN 1 END) AS grp_3_txn_count
     FROM
     (
       SELECT
          id
          ,CASE
             WHEN  dept IN ('040','041') THEN 1
             WHEN  dept IN ('050','051') THEN 2
             WHEN  dept IN ('060')       THEN 3
           END AS grp
          ,trxn_id
        FROM txn_detail
        WHERE dept IN ('040','041','050','051','060')
        GROUP BY 1,2,3
       ) dt
     GROUP BY 1

Dieter

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