Archives of the TeradataForum

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

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

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>>