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
Comments
I couldn’t resist commenting. Perfectly written!
We’re a small grouping of volunteers and opening a brand new scheme inside our community.
Your website provided us with valuable information to function on. You’ve done a
formidable job and our whole community will likely be thankful to you
personally.