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

It doesn't produce round down, it should produce the correct average value. However, if p1 is int in the SegmentAverages table, then yes, it will round to the integer value. If you want an algorithm to randomize, then it's a bit tricky.