Getting Started with Adobe After Effects - Part 6: Motion Blur
A collection of quick technology learning tips from what people around you learn every day

SQL Server 2012 - Calculating running total is easy now..

Dec 11 2011 11:32PM by Ramireddy   

Often in forums, we will see this question, "How to calculate running total?". There are various complex methods available for this and this is one of often discussed topic. Now In SQL Server 2012, OVER clause has been enhanced to calculate running total or moving average

<pre class="brush: plain">
DECLARE @T TABLE
(   
    Val INT
)
INSERT INTO @T (Val)
VALUES (10),(20),(30)

SELECT  SUM(Val) OVER (ORDER BY Val) AS CumulativeTOTAL ,
    	AVG(Val) OVER (ORDER BY Val) AS MovingAverage 
FROM @T 
</pre>
Read More..   [137 clicks]

Published under: SQL Server Tips ·  ·  ·  · 


Ramireddy
2 · 40% · 12972
16
 
0
Knew
 
17
 
0
Incorrect
 
0
Interesting
 
0
Forgotten



Submit

8  Comments  

  • Neat! I presume this has a partition clause?

    commented on Dec 12 2011 1:41AM
    dishdy
    16 · 10% · 3269
  • Yes it does:

    DECLARE @trn TABLE (
        TrnID INT IDENTITY,
        CustID INT,
        Amount SMALLMONEY
    )
    
    INSERT INTO @trn (CustID, Amount)
    SELECT 1, 100 UNION ALL
    SELECT 1, 125 UNION ALL
    SELECT 1, 75 UNION ALL
    SELECT 2, 12 UNION ALL
    SELECT 2, 45
    
    SELECT 
        *,
        SUM(Amount) 
        	OVER (PARTITION BY CustID ORDER BY TrnID) 
        	AS Total
    FROM @trn 
    
    /*
    TrnID CustID Amount  Total
    ----- ------ ------- -------
    1     1      100.00  100.00
    2     1      125.00  225.00
    3     1      75.00   300.00
    4     2      12.00   12.00
    5     2      45.00   57.00
    */
    
    commented on Dec 12 2011 1:56AM
    Jacob Sebastian
    1 · 100% · 32220
  • Learned 2 things. 1. For some reason I was under the impression that we could Order By with Over clause prior to 2012. Wrong! 2. How and when to use this new enhancement from SQL Server 2012.

    Thanks for the post.

    commented on Dec 12 2011 1:56PM
    vanne040
    84 · 2% · 657
  • This is excellent! Simple, yet powerful!

    commented on Dec 12 2011 2:24PM
    Nakul Vachhrajani
    4 · 36% · 11521
  • vanne040,

    Prior to SQL Server 2012, you can use Order By with Over Clause. For example, in row_number() clause "Order By" is necessary. However, in aggregate functions with OVER Clause, we can't use it. Now in SQL Server 2012, we can use OrderBy in Aggregate functions with OVER clause

    commented on Dec 12 2011 7:51PM
    Ramireddy
    2 · 40% · 12972
  • Thanks for correcting my comment. I forgot to add the part with ranking functions which can use Over clause.

    commented on Dec 13 2011 8:16AM
    vanne040
    84 · 2% · 657
  • Thanks for info! I just wonder why it take MS so long to make it possible?

    commented on Dec 14 2011 4:33AM
    Adam Tokarski
    58 · 3% · 1021
  • Beware that omitting the ROWS/RANGE BETWEEN clause makes SQL Server default to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which is disk spooled. To force a memory spool, you should write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which is much faster.

    commented on Feb 16 2012 7:13AM
    Peso
    100 · 2% · 527

Your Comment


Sign Up or Login to post a comment.

"SQL Server 2012 - Calculating running total is easy now.." rated 5 out of 5 by 16 readers
SQL Server 2012 - Calculating running total is easy now.. , 5.0 out of 5 based on 16 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]