locked
running total from column value RRS feed

  • 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



    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

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


    Friday, July 13, 2012 10:35 AM

All replies