TSQL Averages with Rollup

Here is tsql way of getting averages and also rolling up. Below example is using adventureworks database that is available with SQL server 2008. It is getting average pay rate by each department and also overall by the company.

use [AdventureWorks]

select[Department] = case when grouping(d.GroupName) = 1 then 'Company Average'

                              else d.GroupName end,

            [Average Pay Rate] = avg(p.Rate)

from (select [EmployeeID], [Rate] = max([Rate])

            from [HumanResources].[EmployeePayHistory]

            group by [EmployeeID]) p

      inner join [HumanResources].[EmployeeDepartmentHistory] h

                  on h.[EmployeeID] = p.[EmployeeID] and h.[EndDate] is null

      inner join [HumanResources].[Department] d on d.[DepartmentID] = h.[DepartmentID]

group by d.[GroupName]

with rollup

order by 1