>>Hi -
>>
>>Here is an interesting problem. Suppose I have a statement like this:
>>

>>update SegmentAverages set 
>>p1 = (SELECT AVG(p1) FROM allcounts WHERE (allcounts.count_id = SegmentAverages.count_id and allcounts._day <3)),
>>p2 = (SELECT AVG(p2) FROM allcounts WHERE (allcounts.count_id = SegmentAverages.count_id and allcounts._day <3)),
>>p3 = (SELECT AVG(p3) FROM allcounts WHERE (allcounts.count_id = SegmentAverages.count_id and allcounts._day <3)),
>>p4 = (SELECT AVG(p4) FROM allcounts WHERE (allcounts.count_id = SegmentAverages.count_id and allcounts._day <3)),
>>p5 = (SELECT AVG(p5) FROM allcounts WHERE (allcounts.count_id = SegmentAverages.count_id and allcounts._day <3)),
>>p6 = (SELECT AVG(p6) FROM allcounts WHERE (allcounts.count_id = SegmentAverages.count_id and allcounts._day <3)),
>>p7 = (SELECT AVG(p7) FROM allcounts WHERE (allcounts.count_id = SegmentAverages.count_id and allcounts._day <3)),
>>p8 = (SELECT AVG(p8) FROM allcounts WHERE (allcounts.count_id = SegmentAverages.count_id and allcounts._day <3)),
>>p9 = (SELECT AVG(p9) FROM allcounts WHERE (allcounts.count_id = SegmentAverages.count_id and allcounts._day <3)),
>>p10 = (SELECT AVG(p10) FROM allcounts WHERE (allcounts.count_id = SegmentAverages.count_id and allcounts._day <3))

>>
>Try:
>

>update SA set p1 = Av.P1, p2 = Av.P2, etc.
>from SegmentAverages SA INNER JOIN (select Count_id, Avg(1.0*p1) as P1, Avg(1.0*P2) as P2, etc.
>from AllCounts WHERE [_day] < 3 GROUP BY Count_ID) Av ON SA.count_id = Av.Count_id

Thanks Naomi and Thomas. I think your suggestions are basically the same. If this is correct, naomi's code produces a round down in every case. I need to somehow randomize this or develop a rule that will round up when the first instance is larger, and round down when it is smaller. Something like this:

Find the average:

Fn(15+14) = 15
Fn(14+15) = 14
Fn(11+16) = 13
Fn(16+11) = 14