----  Using a common table expression to limit counts and report averages 
       The following example shows the average number of employees reporting
       to managers with Adventure Works database. 

      USE AdventureWorks;
      GO

      WITH DirReps (Manager, DirectReports) AS
      (
          SELECT ManagerID, COUNT(*) AS DirectReports
          FROM HumanResources.Employee
          GROUP BY ManagerID
      )
      SELECT AVG(DirectReports) AS [Average Number of Direct Reports]
          FROM DirReps
      WHERE DirectReports >= 2 ;
      GO