CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2012
Ram
Ram

Reputation: 16864

Rollover sum with grouping in SQL Server

I need to do Rollover sum with grouping in SQL Server

I need to sum of sales within same year for the later quaters.

Data is like shown below

Year Qtr sales

2011         1        10

2011         2        5

2011         5        30

2012         4        30

2012         5        2

I would need the output like below

Year | quater | salestillnowThisYear

2011 1 10

2011 2 15 (which is 10 + 5 from previous quaters within same year)

2011 5 45 ( 10 + 5 + 30 from previous quaters of this year)

2012 4 30

2012 4 32 (30 + 2 which is 30 from previous quater of this year)

Upvotes: 1

Views: 4683

Answers (3)

Pரதீப்
Pரதீப்

Reputation: 93754

There are many ways to achieve Rolling Sum. Here are few options.

CREATE TABLE #test
  (years INT, id INT, sal INT )

INSERT #test
VALUES (2011,1,10),
       (2011,2,5),
       (2011,5,30),
       (2012,4,30),
       (2012,5,2)

Method 1 : Using Correlated Sub-Query

SELECT *,
       (SELECT Sum(sal)
        FROM   #test b
        WHERE  a.years = b.years
               AND a.id >= b.id)
FROM   #test a

Method 2: Using Inner Join 

SELECT a.years,
       a.id,
       Sum(b.sal) AS total
FROM   #test a
       INNER JOIN #test b
               ON a.id >= b.id
                  AND a.years = b.years
GROUP  BY a.years,
          a.id

Method 3 : Using SUM() with the OVER() clause. Check here for more info on Over Clause

SQL FIDDLE DEMO

SELECT a.years,
       a.id,
       sal,
       Sum(sal)
         OVER(
           partition BY years
           ORDER BY id rows UNBOUNDED PRECEDING) AS runningtotal
FROM   #test

Note: Method 3 solution works from SQL SERVER 2012+

Upvotes: 3

Dgan
Dgan

Reputation: 10295

create table test(year1 int,quarter1 int,sales int)

insert into test
select 2011         ,1        ,10 UNION ALL

select 2011         ,2        ,5 UNION ALL

select 2011         ,5        ,30 UNION ALL

select 2012         ,4        ,30 UNION ALL

select 2012         ,5        ,2

Try this:

 SELECT a.year1
           , a.quarter1
           , SUM(b.sales) AS total

FROM test a INNER JOIN test b
     ON  a.quarter1 >= b.quarter1
     AND a.year1 = b.year1

GROUP BY a.year1
           , a.quarter1
ORDER BY a.year1

OUTPUT

2011    1   10
2011    2   15
2011    5   45
2012    4   30
2012    5   32

Upvotes: 2

vhadalgi
vhadalgi

Reputation: 7189

Try using sum() with Partition By

select *,sum(sales)over(partition by Year order by Year) as salestillnowthis  from table

More Info

Upvotes: 0

Related Questions