What was that - sample data, expected results, or actual results?

Anway, if I understand correctly, your question is why the SELECT statement returns more than one row? The answer is in the GROUP BY clause. You'll get one row for each combination of tms.OfferID and tms.Revision, and your query will count the distinct values of ContractID in each of those rows.

John