Average calculations

[YNWA]

Hi,

I have a table with measurements in for each person.

Field 1 = 1st Measurement
Field 2 = 2nd Measurement

What I need to do is calculate the weight loss between the two measurements for each record, then avarage it out for the whole table.

If no one has a weight measurement, then the nulls need to be excluded.

I also need it to filter and only look at records whos field course end date field (in another table) has a date in

So if I have
ID = Measure1 = Measure 2
ID1 - 10 - 5
ID2 - 8 - 8
ID3 - 16 - 17

I need the query to create a new field to get the differences between Measure 1 and Measure 2. Then average it out over the rest of the table.

So example above should read

Difference
-5
0
+1

So average is 4/3 = 1.33.

 

[Taruz]

select  
          Measurecount, 
          Difference, 
          [Difference]/[Measurecount] as MAvg
from (select count(ID) as Measurecount, (
                   select sum([Measure2]-[Measure1]) from table_name) as Difference
from table_name)  as tt

Hi...

I hope I understand correctly..