The Best Way to Obtain Percentages
Published: 6th September 2014
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.
Comments:
NB: Comments will only appear once they have been moderated.