SQL - Cumulative Sales Percentage

Question

I have a table with two columns - you can call the table below SalesSummary, for example:

Rank, Sales Dollars
1, $700
2, $200
3, $100

I would like to write a query to add a new column - cumulative sales percentage:

Rank, Sales Dollars, Cumulative Sales Percentage
1, $700, 70%
2, $200, 90%
3, $100, 100%

The key part is cumulative, for example the Rank 2 entry should be 90% because it adds the $700 + $200 together over the $1000 total to get the 90%.

Any advice? Thank you very much for any help!!

How did the Community Bot know this is a duplicate ? – Accountant م Apr 14 '18 at 14:46 — Accountant م, Apr 14 '18 at 14:46

score 6 · Accepted Answer · answered Apr 14 '18 at 00:16

Use window functions. Here is how you calculate the cumulative amount and the ratio:

select t.*,
       sum(t.sales) over (order by rank) / sum(t.sales) over ()
from t
order by rank;

Note: If t.sales is an integer, then the division will be 0. You can multiply by 1.0 to get a number between 0 and 1 or by 100.0 to get a number between 0 and 100.

jose_bacoy · Answer 2 · 2018-04-14T00:10:38.320

If sales is in decimal or integer then you can compute it this way. I convert the sales and cum percent into strings before adding $ and % sign.

SELECT t1.rank,
       '$' + convert(varchar(20), t1.sales) as "Sales Dollars",
       convert(varchar(4), sum(t2.sales)*100/(select 
   sum(sales) from tbl)) + '%' as "Cumulative Sales Percentage"
FROM tbl t1
INNER JOIN tbl t2 
ON t1.rank >= t2.rank
GROUP BY t1.rank,  t1.sales
ORDER BY t1.rank;

SQL - Cumulative Sales Percentage

2 Answers2