Archives of the TeradataForum

Message Posted: Wed, 27 Nov 2002 @ 10:57:44 GMT

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

Hi anomy.anom,

try this

create table test
(
        id integer,
        med_group char(1),
        med_value integer
) primary index (id);

insert into test values (1,'A',1);
insert into test values (2,'A',2);
insert into test values (3,'A',4);
insert into test values (4,'A',5);

insert into test values (1,'B',1);
insert into test values (2,'B',6);
insert into test values (3,'B',7);

select  med_group,
                sum(case  when eval_ind = 0 and (rec_no = num_of_row/2 or rec_no = (num_of_row/2)+1) then med_value
                              when eval_ind = 1 and rec_no = (num_of_row+1)/2 then med_value
                              else 0
                    end
           ) /  (case when eval_ind = 0 then 2 else 1 end) from
                (
                 select  t.med_group,
                                 t.med_value,
                         m.num_of_row,
                             m.eval_ind,
                         csum(1,t.med_value) as rec_no
                 from
                                (
                                 select  med_group,
                                                 count(*) as num_of_row,
                                         num_of_row mod 2 as eval_ind
                                 from test
                                 group by med_group
                              ) as m,
                        test t
                 where t.med_group = m.med_group
                 group by t.med_group
                 ) as t
group by med_group,eval_ind
order by med_group
;

drop table test;

Ulrich

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