August 18, 2013

Using SUM OVER in SQL Server to Get % Sales




In some of our previous posts Aggregates without GroupBy SQL Server and SQL Server 2012 - Running total with SUM function‏, we have seen how to use SUM and OVER to calculate running totals and count without using the GroupBy clause.

Today we will use both the SUM and OVER function together to calculate Percentage(%) Sales
Consider the following table:

Sum-Over-Sample

To calculate what % of Qty sold each Product Code accounts for, use this code using SUM and OVER

SELECT [ProductCode], SUM(QTYSOLD) AS [Sold],
       100.0 * SUM(QTYSOLD) / SUM(SUM(QTYSOLD)) OVER () AS [%]
FROM SomeTable
GROUP BY [ProductCode]


The code is pretty simple to understand. We are calculating the total items sold per Product and using a simple formula to calculate the % each Product Code has sold using SUM and OVER

OUTPUT

image


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

0 Responses to "Using SUM OVER in SQL Server to Get % Sales"
 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions