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.
By adding a simple sum, we can display how much each customer spent.
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.
Just to confirm, using Excel, we come up with the same answer (there is some rounding).
Jim Salasek's SQL Server Blog 