Archives of the TeradataForum

Message Posted: Tue, 28 Jul 2009 @ 19:52:56 GMT

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

Hi Fred,

You can use an aggregate function ( max function in this case ) to get the required result.

Ex :

     select
     location
     ,unit_ID
     ,max(Fault3)
     ,max(Fault35)
     ,max(Fault155)
     ,max(Fault156)
     FROM
     (
     Select t.location, t.unit_ID,
                     (Case When t.fault=3 Then t.FltCnt Else 0 End) as Fault3,
                     (Case When t.fault=35 Then t.FltCnt Else 0 End) as Fault35,
                     (Case When t.fault=155 Then t.FltCnt Else 0 End) as Fault155,
                     (Case When t.fault=156 Then t.FltCnt Else 0 End) as Fault156,
     From (
     Select location, unit_ID, fault, Count(fault) as FltCnt
     From database
     Where location = 4443
       And unit_ID = 3124
       And fault=3 OR fault=35 OR fault=155 or fault=156
     Group by location, unit_ID, fault
     ) t
     ) t1
     group by location,unit_ID

Thanks and regards,

Vikrant

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