Calculate Running Percentage In SQL

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

Solution 1:

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.