Calculate Percentage of Total

When creating queries for a data warehouse, you may need to calculate what percentage of each customers portion is to the total.  This can show you what percentage of total sales each customer makes up.

Below is a listing of our fact table.  This shows sales amount and customerid.  We would like to find out how much each customer spent.

ov1

 

By adding a simple sum, we can display how much each customer spent.

ov2

 

But now we need to also know what percentage of each customers sales to total sales.  If using SQL Server 2012 or higher, we can use analytic windowing functions.  To fully read about the power of these please follow this link to learn more:  https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-2017

For a quick definition from the link above:

“Determines the partitioning and ordering of a rowset before the associated window function is applied. That is, the OVER clause defines a window or user-specified set of rows within a query result set. A window function then computes a value for each row in the window. You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results.”

 

By using OVER () , this gives us the percentage of total sales for each customerid.

ov4

 

Just to confirm, using Excel, we come up with the same answer (there is some rounding).

ov5

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s