The Best Way to Obtain Percentages
Published: Mar 25, 2016
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

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.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London



Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron