Calculate running percentage in SQL

Question

Could anyone help me with calculating a running percentage in SQL? Suppose we have the following data:

column 1   column 2
  1          500
  2          499
  3          200
  4           50
  5           10
  6            5

I want to know what percentage of column 2 makes up 80%. In the example the sum for column 2 is 12664. The first three rows make up for 94%: (500+499+200)/sum(column 2) = 94%. Following this I will conclude that 3/6 = rows makes the total of 80%.

How do I do this in SQL?

I recommend you'd edit your question, because it has a few problems making it unlikely folks will want to invest time in answers. First, work on the [formatting](http://stackoverflow.com/editing-help) issues, I recommend you format the sample dataset as code. Second, tell us what you've tried so far, what code you have, and why your current solutions don't work. Third is a minor point, if you're able, review your spelling, grammar, interpunction and the "flow" of your question (read it to yourself if needed). — Jeroen, Oct 17 '12 at 09:54
It made more sense. I've tried to help by editing and further improving your question. If you feel I've made a mistake somewhere feel free to re-edit it. One last thing you could clarify: why did you mention "SSRS" in the question title and tags? The question itself seems to be mainly about SQL. Would an answer with an SSRS expression be acceptable too? — Jeroen, Oct 17 '12 at 13:22
Thank you so much for your help, I only need an SQL solution. — Gurru, Oct 17 '12 at 13:57

score 4 · Answer 1 · edited May 23 '17 at 10:29

You just about have a duplicate of this question on running totals, though you'd have to extend the answers to calculate a running percentage. I recommend reading through the answers from the linked question (note that MSSQL 2012 has SUM...OVER syntax, hurray!), because they are more in depth than my suggestion below.

For your case, you could do something like this to get a running percentage:

DECLARE @table TABLE(col1 INT, col2 FLOAT);

INSERT INTO @table  (col1, col2) 
VALUES (1, 500), (2, 499), (3, 200), (4, 50), (5, 10), (6, 5)

DECLARE @col2total FLOAT = (SELECT SUM(col2) FROM @table)

-- Using subqueries
SELECT      col1, 
            col2, 
            (SELECT SUM(col2) FROM @table sub WHERE sub.col1 <= base.col1) 
            / @col2total
            * 100 AS RunningPercentage
FROM        @table base
ORDER BY    col1

-- Using cross join
SELECT      t1.col1,
            t1.col2,
            SUM (t2.col2) RunningTotal,
            SUM (t2.col2) / @col2total * 100 RunningPercentage
FROM        @table t1 CROSS JOIN @table t2
WHERE       t1.col1 >= t2.col1
GROUP BY    t1.col1, t1.col2
ORDER BY    t1.col1

With this it should be easy to get the top or bottom 80% any way you'd like.

Calculate running percentage in SQL

1 Answers1

Linked