Again, this is something that I decided to write about because I see code in a lot of places used in a lot of different ways and within most MI there is always a need to obtain a percentage figure across a dataset and, for some reason, no-one seems to use SUM with OVER but rather they choose an older, longer method.
Therefore I thought I would make note of this newer way to achieve this in the hope that more people realise how much easier it is and start to utilise it within their t-SQL.
Firstly we need a dataset on which to work. I’ve made a small table using the AdventureWorks2012 database which simply shows total sales by sales person for the month of July in 2005:
use AdventureWorks2012
go
declare @sales table
(
fullName varchar(100),
salesTotal money
)
insert into @sales
select p.firstName + ' ' + p.LastName as fullName,
sum(TotalDue) totalSalesValue
from sales.SalesOrderHeader s
join sales.vSalesPerson p
on s.SalesPersonID = p.BusinessEntityID
where orderDate >= '2005-07-01'
and orderDate < '2005-08-01'
and salesPersonID is not null
group by p.firstName + ' ' + p.LastName
So, in true MI style, we have this summarised data and we want to know what percentage of sales total each sales person was responsible for.
The traditional way of achieving this, and which I still see used all over the place, is to use a pre-calculated variable as follows:
-- pre-calculate total sales into variable
declare @totalSales money
select @totalSales = sum(salesTotal)
from @sales
-- use the variable to calculate a percentage
select fullName, salesTotal / @totalSales * 100 as percentageSales
from @sales
order by percentageSales desc
Another method is to simply use a subquery to create a total and then cross apply that into the table as follows:
-- cross join into a separately calculated total
select fullName, salesTotal / totalSales * 100 as percentageSales
from @sales s
cross apply
(
select sum(salesTotal) totalSales
from @sales
) x
order by percentageSales desc
However, by far the cleanest and best method is to use OVER() which I rarely see used outside of functions such as ROW_NUMBER and RANK, but which can be very powerful in other circumstances such as this:
-- using OVER()
select fullName, salesTotal / sum(salesTotal) over() * 100 as percentageSales
from @sales
order by percentageSales desc
As you can see this is much easier to read, is less complicated, and less code.