SQL Server Fun – 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. 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
  1. 7 years ago
  2. 5 years ago

Leave a Reply to MinhKHostler Cancel reply

Your email address will not be published. Required fields are marked *