# Running Total Percentage • ### Question

• Hi All,

I have a requirement to calculate the table with rows like below and wanted to calculate the percentage of the data

 EntriesRemaining NoOfEntries 100 175 0.856516977 149.890471 0.699488491 104.8466594 0.71297989 74.75355971 0.944871795 70.63253014 0.900949797 63.63636365

So from above data 100% is 175 and next row I want is 85% of 175 which brings 149.89 this result is used to calculate the percentage of next row which is 69% of 149.89 which is 104.84.. and so on.

Monday, February 22, 2016 8:20 PM

• Using Stefan's sample table, here is the solution:

DECLARE @Sample TABLE ( OrderNo INT, EntriesRemaining FLOAT, NoOfEntries FLOAT, PRIMARY KEY ( OrderNo ) );

INSERT INTO @Sample
VALUES   ( 1, 100, 175 ),
( 2, 0.856516977, 149.890471 ),
( 3, 0.699488491, 104.8466594 ),
( 4, 0.71297989, 74.75355971 ),
( 5, 0.944871795, 70.63253014 ),    ( 6, 0.900949797, 63.63636365 );

SELECT *, 175 * power(1E1, SUM(LOG10(EntriesRemaining))                           OVER (ORDER BY OrderNo                                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))
FROM    @Sample
WHERE  OrderNo > 1

The problem is a running total. There is no product aggregator in SQL Server. However, as long as all values in the product are positive, we can use the fact that a*b = n^(logn(a) + logn(b)), which is what I use here. (With n = 10.)

The solution requires SQL 2012 so that we can use the window aggregate.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Monday, February 22, 2016 11:15 PM

### All replies

• First of all: This is absolutly not possible with this data.

But maybe you're looking for something like this (SQL Server 2012+):

```DECLARE @Sample TABLE ( OrderNo INT, EntriesRemaining FLOAT, NoOfEntries FLOAT, PRIMARY KEY ( OrderNo ) );

INSERT INTO @Sample
VALUES	( 1, 100, 175 ),
( 2, 0.856516977, 149.890471 ),
( 3, 0.699488491, 104.8466594 ),
( 4, 0.71297989, 74.75355971 ),
( 5, 0.944871795, 70.63253014 ),
( 6, 0.900949797, 63.63636365 );

SELECT	*,
LAG( NoOfEntries, 1, NULL ) OVER ( ORDER BY OrderNo ASC),
EntriesRemaining * LAG( NoOfEntries, 1, NULL ) OVER ( ORDER BY OrderNo ASC)
FROM	@Sample;```

Monday, February 22, 2016 8:50 PM
• Hi Stefan, Thanks for the quick response, I need to populate the No Of Entries column using the 100% number which in my case is 175, using 175 I get the 85% value of 175 and use that value to get the 69% and so on...
Monday, February 22, 2016 9:00 PM
• ```create table Accessinfo  (id int identity(1,1), EntriesRemaining decimal(10,2),	NoOfEntries decimal(10,2))

Insert into Accessinfo values(1,175) ,
(0.856516977,149.890471),(0.699488491,104.8466594)
,(0.71297989,74.75355971),(0.944871795,70.63253014)
,(0.900949797,	63.63636365)

declare @cnt int=(select count(*) from  Accessinfo)
;with mycte as
(
SELECT id, EntriesRemaining,   Cast(NoOfEntries as decimal(10,2)) as product1
FROM Accessinfo
where EntriesRemaining=1
Union all
Select a.id, a.EntriesRemaining,   Cast(product1*a.EntriesRemaining as decimal(10,2))   as cnt from mycte m
join Accessinfo a on a.id=m.id+1
)

select *  from mycte
order by id

drop table Accessinfo```

• Proposed as answer by Monday, February 22, 2016 9:36 PM
Monday, February 22, 2016 9:15 PM
• It is not clear how did you get 69% and so on. What do you use to get this 69% (which numbers)?

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

Monday, February 22, 2016 9:19 PM
• Assuming that you have a column with all the desired percentages prepopulated,

```create  table #mytemp
(ID INT IDENTITY(1,1),EntriesRemaining numeric(5,2),NoofEntries numeric(5,2))
INSERT INTO #mytemp VALUES(100,175),(0.85,NULL),(0.69,NULL)

SELECT * FROM #mytemp where EntriesRemaining=100
UNION
select t1.ID,t1.EntriesRemaining ,t1.EntriesRemaining*t2.NoofEntries as NoofEntries
FROM #mytemp t1
JOIN #mytemp t2 on t1.ID-1=t2.ID
where t1.ID>1
```

Aparna

• Proposed as answer by Monday, February 22, 2016 9:41 PM
Monday, February 22, 2016 9:31 PM
• Using Stefan's sample table, here is the solution:

DECLARE @Sample TABLE ( OrderNo INT, EntriesRemaining FLOAT, NoOfEntries FLOAT, PRIMARY KEY ( OrderNo ) );

INSERT INTO @Sample
VALUES   ( 1, 100, 175 ),
( 2, 0.856516977, 149.890471 ),
( 3, 0.699488491, 104.8466594 ),
( 4, 0.71297989, 74.75355971 ),
( 5, 0.944871795, 70.63253014 ),    ( 6, 0.900949797, 63.63636365 );

SELECT *, 175 * power(1E1, SUM(LOG10(EntriesRemaining))                           OVER (ORDER BY OrderNo                                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))
FROM    @Sample
WHERE  OrderNo > 1

The problem is a running total. There is no product aggregator in SQL Server. However, as long as all values in the product are positive, we can use the fact that a*b = n^(logn(a) + logn(b)), which is what I use here. (With n = 10.)

The solution requires SQL 2012 so that we can use the window aggregate.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Monday, February 22, 2016 11:15 PM