Archives of the TeradataForum

Message Posted: Mon, 04 Oct 2004 @ 17:27:19 GMT

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

Greetings Fred,

If I understand your question, you are wanting to 'roll-up' the average age of the customer by transaction type. If that's correct then something like this might be the ticket.

     select  c.transid
             , c.ages / c.trans  avg_age_per_trans
     from (  select  s.transTyp
                     , sum(s.age) ages
                     , count(*)  trans
             from (  select  , a.custno
                             , a.transTyp
                             ,  ((current_date - a.date_of_birth) year(4)) (dec(4,0) ) age
                     from
                         trans a
                     ,   cust b
                     where
                        a.custno = b.custno and
                        b.date_of_birth is not null  ) s
             group by 1   ) c
     ;

I'm making assumption that something similar to transaction type or id exist within your data.

Regards,

Paul

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