Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 04 Oct 2004 @ 14:04:51 GMT


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


Subj:   Calculating average age per transaction
 
From:   Fred Easey

Hi,

I have two tables, one is trans and has a record of every customer transaction as well as their cust no. The other is cust which has a record of each customers DOB and their cust no.

I want to work out average age per transaction, eg if I have 2 transactions, 1 by a 20 yr old and the other by a 30 yr old, then the average age of transaction = sum of ages/count of transactions = 50/2 = 25.

I tried to write some Teradata SQL to work this out using my two tables thus:

     select
        sum(current_date - a.date_of_birth year(4))/count(*) as average_age
     from
        trans a,
        cust b
     where
        a.custno = b.custno and
        b.date_of_birth is not null
     ;

When I run this I get the following error message:

     ERROR: Teradata row not delivered (trget): Interval field overflow.

If I take the sum out...:

     select
        current_date - a.date_of_birth year(4) as age
     from
        trans a,
        cust b
     where
        a.custno = b.custno and
        b.date_of_birth is not null
     ;

... then the script runs fine and I get a list of ages. Presumably summing years and dividing by count(*) is the issue, how can I fix this?

Please advise,


Cheers,

Fred

UK



     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016