running total from column value

Question

SELECT 
      a1.Docket_Category, 
      COUNT(a1.Docket_Id) as  Dockets ,
      (count(a1.Docket_Id)) --*100) --/(SELECT Count(Docket_id) FROM SLADB.dbo.DocketTB) Pct_To_Total
      FROM SLADB.dbo.DocketTB a1
      WHERE Docket_Category IS NOT NULL       
      GROUP BY a1.Docket_Category      
      ORDER BY Dockets DESC

How can I add a column showing a running total on the Pct_To_Total column please

Answer 1

1) assign a row number to each row using row_number() and add extra column for running total and insert the data into a temporary table.

2) start a while loop from @i = 1 to count of rows in temporary table

3) update table on the runningTotal column, set its value =  sum(values) where row_number < @i

4) set @i += 1

Answer 2

Please see if the following thread would help your requirement.

http://social.msdn.microsoft.com/Forums/en/transactsql/thread/c0bdbd65-4f44-4380-8218-a90eb1281ed4

---

Murali Krishnan

Answer 3

;With CTE1
As
(SELECT 
			a1.Docket_Category, 
			COUNT(a1.Docket_Id) as  Dockets
FROM		SLADB.dbo.DocketTB a1
WHERE		Docket_Category IS NOT NULL       
GROUP BY	a1.Docket_Category      
)
, CTE2
As
(
Select		Docket_Category, Dockets, SUM(Dockets) Over() AS Total_Dockets
			, ROW_NUMBER() Over(Order by Dockets) As RN
From		CTE1
)

Select		A.Docket_Category, A.Dockets, A.Total_Dockets
			, SUM(B.Dockets) As Running_total
			, SUM(B.Dockets) * 1.0 / A.Total_Dockets As Runing_Total_Percent
From		CTE2 A
				Inner Join
			CTE2 B
				On A.Docket_Category = B.Docket_Category
					And B.RN <= A.RN			
Group By	A.Docket_Category, A.Dockets, A.Total_Dockets
Order By	A.Dockets
		

---

 | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

Hate to mislead others, if I'm wrong slap me. Thanks!

Answer 4

or, you can use a table level variable to store all data with an extra column for running total. using a cursor, you can update the running total, the way you want to have it.

regards

joon

Answer 5

The OVER clause might also be helpful.

http://msdn.microsoft.com/en-us/library/ms189461.aspx

Answer 6

An other running total query http://www.kodyaz.com/t-sql/sql-running-total-in-sql-server-2008.aspx which uses CTE expression

---

SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

Answer 7

use rollup or computed column.

http://shamas-saeed.blogspot.com/2011/05/creating-computed-calculated-column-in.html

---

Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

Answer 8

Take a look at the first answer in this thread

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/c0bdbd65-4f44-4380-8218-a90eb1281ed4/#a8f0b7a6-8241-4fea-a383-14175d72b26a

---

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


My blog

Answer 9

check rollup and cube.

that might help with your logic.

Answer 10

Hi,

Check the following blog post to calculate the running totals: http://sqlwithmanoj.wordpress.com/2012/04/13/sql-server-2012-a-k-a-denali-new-feature-enhanced-over-clause-part1/

This post lists options to calculate Running totals with 2012's and prior version's approach.

---

~manoj | email: http://scr.im/m22g
http://sqlwithmanoj.wordpress.com
MCCA 2011