How to get the percentage of a total with MSSQL
To get the percentage of a total, you will need to:
- Calculate the grand total.
- Get the subtotal for the key value.
- Do: (subtotal / total) * 100
The problem is: How to get at the same time the subtotal and the grand total for the same row?
The answer is: Microsoft SQL Server, like DB2 and Oracle, provides a windowing of the sum.
Data used
SELECT customerid,
salesorderid,
subtotal
FROM sales.salesorderheader
WHERE customerid <= 15
ORDER BY customerid;
go
salesorderid,
subtotal
FROM sales.salesorderheader
WHERE customerid <= 15
ORDER BY customerid;
go
customerid salesorderid subtotal ----------- ------------ --------------------- 1 43860 13216.0537 1 44501 23646.0339 1 45283 34066.1881 1 46042 31423.5209 2 46976 9216.3596 2 47997 4949.8589 2 49054 1574.1247 2 50216 1751.5987 2 51728 3534.1671 2 57044 4106.6501 2 63198 3668.7336 2 69488 822.009 3 44124 18555.8149 3 44791 17419.6078 3 45568 18285.6382 3 46377 11796.1804 3 47439 71112.1316 3 48378 51198.5404 3 49538 27899.9579 3 50748 47138.0027 3 53616 83436.1754 3 59011 40109.0555 3 65310 15431.5196 3 71889 31559.7518 4 46642 118394.1258 4 47658 119637.8301 4 48740 97037.566 4 49821 94133.8413 4 51122 73368.0931 4 55233 73455.3404 4 61193 67414.7924 4 67292 62472.63 5 47436 20720.6175 5 48374 9479.102 5 49534 8456.292 5 50746 9134.6608 5 53607 18463.4533 5 59014 18431.5666 5 65307 7950.2303 5 71890 10729.5078 6 53596 59.9326 6 59030 545.0322 6 65287 559.0108 6 71915 2137.231 7 46613 3569.4338 7 47667 2449.6033 7 48765 1769.1174 7 49861 1596.2947 8 51874 8703.4707 8 57190 8888.6076 8 63220 5571.0288 8 69565 4293.256 9 48092 290.1614 9 49170 4903.775 9 50234 586.989 9 51758 1605.5854 9 57072 3221.8384 9 63234 632.4859 9 69452 4148.3876 10 47411 45089.9298 10 48350 42422.9572 10 49467 45892.9666 10 50698 31903.3672 10 53527 88901.8475 10 58972 97077.5808 10 65206 83047.2353 10 71841 102044.1343 11 43853 31625.9064 11 44495 4890.3356 11 45276 7307.9784 11 46035 14683.3568 12 46953 79060.5774 12 47980 27201.6262 12 49100 20749.6253 12 50203 48405.2778 12 51703 61466.1143 12 57023 42687.3909 12 63141 4720.3618 12 69401 21607.0277 14 53608 2091.01 14 53609 3188.7342 14 59033 967.822 14 59053 402.222 14 65090 98.774 14 65305 318.4545 14 71692 197.548 14 71910 694.4405 15 53566 76765.5159 15 59018 48415.5983 15 65273 15777.1566 15 71933 35092.7264 (91 row(s) affected)
Percentage of total
This involves 2 things:
- An inline view
- A sum (..) over (partition...)
SELECT DISTINCT customerid,
Cust AS 'Customer total',
Total AS 'Gd total sales',
(Cust/Total) * 100 AS '% of total sales'
FROM (
SELECT customerid,
SUM(subtotal) OVER () AS 'Total',
SUM(subtotal) OVER (partition BY customerid) AS 'Cust'
FROM sales.salesorderheader
WHERE customerid <= 15) abc
WHERE customerid <= 15
ORDER BY '% of total sales' DESC, customerid;
go
Cust AS 'Customer total',
Total AS 'Gd total sales',
(Cust/Total) * 100 AS '% of total sales'
FROM (
SELECT customerid,
SUM(subtotal) OVER () AS 'Total',
SUM(subtotal) OVER (partition BY customerid) AS 'Cust'
FROM sales.salesorderheader
WHERE customerid <= 15) abc
WHERE customerid <= 15
ORDER BY '% of total sales' DESC, customerid;
go
customerid Customer total Gd total sales % of total sales ----------- --------------------- --------------------- --------------------- 4 705914.2191 2515524.1652 28.06 10 536380.0187 2515524.1652 21.32 3 433942.3762 2515524.1652 17.25 12 305898.0014 2515524.1652 12.16 15 176050.9972 2515524.1652 6.99 5 103365.4303 2515524.1652 4.10 1 102351.7966 2515524.1652 4.06 11 58507.5772 2515524.1652 2.32 2 29623.5017 2515524.1652 1.17 8 27456.3631 2515524.1652 1.09 9 15389.2227 2515524.1652 0.61 7 9384.4492 2515524.1652 0.37 14 7959.0052 2515524.1652 0.31 6 3301.2066 2515524.1652 0.13 (14 row(s) affected)
- sum(subtotal) over () as 'Total' calculates the grand total for the data selected in the WHERE of the outside query.
- sum(subtotal) over (partition by customerid) as 'Cust' calculates the subtotal amount for each customer and resets it for the next customer. That's the windowing function of the sum.
- Distinct is there because if not, it would show a line for each sales order, and we want a line for each customer.
- It's critical to realize that the windowing functions are applied AFTER the where clause is evaluated.
- This allows to calculate the correct grand total sum(subtotal) over().
- The selection criteria MUST NOT be in the inline view, but on the outside query.