May 12, 2014

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:


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



Did you like this post?
kick it on
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



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

Copyright © 2009-2016 All Rights Reserved for by Suprotim Agarwal | Terms and Conditions