Answered by:
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- Moved by Eileen Zhao Friday, July 13, 2012 9:53 AM (From:Data Mining)
Wednesday, July 11, 2012 2:36 PM
Answers
-
;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!
- Edited by Steven Wang - Shangzhou Friday, July 13, 2012 10:43 AM
- Marked as answer by Iric Wen Monday, July 23, 2012 5:38 AM
Friday, July 13, 2012 10:35 AM
All replies
-
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
Friday, July 13, 2012 10:11 AM -
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
Friday, July 13, 2012 10:17 AM -
;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!
- Edited by Steven Wang - Shangzhou Friday, July 13, 2012 10:43 AM
- Marked as answer by Iric Wen Monday, July 23, 2012 5:38 AM
Friday, July 13, 2012 10:35 AM -
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
Friday, July 13, 2012 10:41 AM -
The OVER clause might also be helpful.
Friday, July 13, 2012 10:45 AM -
An other running total query http://www.kodyaz.com/t-sql/sql-running-total-in-sql-server-2008.aspx which uses CTE expressionFriday, July 13, 2012 11:23 AM
-
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
- Proposed as answer by Shamas Saeed Thursday, July 19, 2012 1:51 PM
Friday, July 13, 2012 12:12 PM -
Take a look at the first answer in this thread
For every expert, there is an equal and opposite expert. - Becker's Law
My blogTuesday, July 17, 2012 4:44 AM -
check rollup and cube.
that might help with your logic.
Tuesday, July 17, 2012 5:41 AM -
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 2011Tuesday, July 17, 2012 6:26 AM