Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


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

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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

Categories


© Copyright 2020 SQLTraining Ltd.