Archives of the TeradataForum
Message Posted: Mon, 04 Oct 2004 @ 14:04:51 GMT
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?
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|