Archives of the TeradataForum

Message Posted: Tue, 28 Jul 2009 @ 15:52:54 GMT

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

Hi Everyone,

First let me apologize, I'm new to SQL and Teradata. I have written the following query:

     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
     Order by location, unit_ID, fault

The actual query is more detailed than this but this gives you the key idea. From this query I get the results that I expect and have checked to make sure it is correct :

     Location   unit_ID   Fault   FltCnt
         4443      3124       3        6
         4443      3124      35        1
         4443      3124     155        1
         4443      3124     156        3

But the results I am really looking for is :

     Location   unit_ID   Fault3   Fault35   Fault155   Fault156
         4443      3124       6          1          1          3

I then tried using Case statements as such:

     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

The results of this, instead of getting one row, I get :

     Park   ID          Flt_3   Flt_35   Flt_155   Flt_156
     4443   840073124       0        0         0         3
     4443   840073124       6        0         0         0
     4443   840073124       0        1         0         0
     4443   840073124       0        0         1         0

The right answers but I really wanted one row. What am I doing wrong?

Thanks,

Fred

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