I have found a simple workaround in cases where you cannot use subqueries...
SELECT AVG(COALESCE([Ship Cost], 0)) * COUNT(DISTINCT [Tracking #]) ... GROUP BY [ORDER #]