TSQL Max and Over Clause

You can use the MIN, MAX, AVG and COUNT functions with the OVER clause to provide aggregated values on multiple rows.

For instance:

SELECT Name

 , MIN(Rate) OVER (PARTITION BY edh.DepartmentID) AS MinSalary

 , MAX(Rate) OVER (PARTITION BY edh.DepartmentID) AS MaxSalary

 , AVG(Rate) OVER (PARTITION BY edh.DepartmentID) AS AvgSalary

 ,COUNT(edh.BusinessEntityID) OVER (PARTITION BY edh.DepartmentID) AS EmployeesPerDept

FROM EmployeePayHistory AS eph

      JOIN EmployeeDepartmentHistory AS edh

            ON eph.BusinessEntityID = edh.BusinessEntityID

      JOIN Department AS d

            ON d.DepartmentID = edh.DepartmentID

http://msdn.microsoft.com/en-us/library/ms187751.aspx