TOTAL MESSAGES
select count(*) from Message as m where m.DID in (select DID from Dataset where CollectDate < 19980101000000 and CollectDate >= 19970101000000);
TRY

select count(*) from Message as M, DataSet as D where m.DID = D.DID and D.CollectDate < 19980101000000 and D.CollectDate >= 199701010000000;

TOTAL MESSAGE PATH COMPONENTS
select count(*) from MsgPath as m where m.MsgPathID in (select MsgPathID from Message as m where m.DID in (select DID from Dataset where CollectDate < 19980101000000 and CollectDate >= 19970101000000));


TRY 

select count(*) from MsgPath as p, Message as m,  Dataset as D where p.MsgPathID = m.MsgPathID and m.DID = D.DID and d.CollectDate < 19980101000000 and d.CollectDate >= 19970101000000));



THEN JUST DIVIDE TOTAL MESSAGE PATH COMPONENTS BY TOTAL MESSAGES

we can run 

select count(*) from Message as m where m.DID in (1,2,3);
and
select DID from Dataset where CollectDate < 19980101000000 and CollectDate >= 19970101000000;

but not together as we'd like - do we need SQL 4.0?


we still need then to query the msgpath table as well

SELECT MAX(id) FROM MsgPath, DataSet where CollectDate >=
19970101000000 and CollectDate< 199801010000;

This gives us the total number of paths 

SELECT MAX(MsgPathID) FROM MsgPath;

This gives us the total number of path elements contained in the
paths.

These queries are very fast since the id from message path 
is the primary key (indexed) and there is also an index on
MsgPathID.

we then divide the number of path elements by the number of paths
to get the average path length.

example: for paix the average path length was 20537453/5300026 = 3.87