Running sum simplified in SQL 2012




Running sum is a regular requirement in query for developer and many times developer has to run number  of complex  queries or self joins to get running sum
But now since SQL  2012 its very easy and funny and simplified.


Lets see this exciting but simple task
 
In 2012 we can use order by also while using aggregate window function. 
In 2008 we can use only partitioned by clause but now in SQL 2012 has simplified our task 

Lets see this 

  
 CREATE TABLE test
     (
          id    INT,
          value INT,
          statsdate date
     )

 DECLARE @i INT

 SET @i = 1

 WHILE @i <= 10
     BEGIN
         INSERT INTO test
         SELECT @i,
                @i * 10,
                getdate()+ @i/3

         SET @i = @I + 1
     END

 SELECT *
 FROM   test

 

Running sum for entire table
 

We want to get running sum for all data in the table
 
 Here is the query 

WITH cte
     AS (SELECT *,
                Sum(value) OVER (ORDER BY id) AS runningsum
         FROM   test)
SELECT *
FROM   cte



Running sum reset on date


Now we want running sum but datewise
So when date changes we want to reset running sum
We have to use partition by clause using statsdate column
 
Here is the query
 


WITH cte
      AS (SELECT *,
                 Sum(value) OVER (ORDER BY id)  AS runningsum,
                 Sum(value) OVER (partition BY statsdate ORDER BY id) AS runningsum_partitioned
          FROM   test)
 SELECT *
 FROM   cte


 




No comments:

Post a Comment

Amazon Contextual Product Ads

Popular Posts