Running total on multiple columns

Question

I have a table like this.

Date               Item               BuyItem
20150101           Mouse              10
20150101           Keyboard           100
20150202           Mouse              20
20150202           Keyboard           200

I want to query like this.

Date               Item               RunningTotal
20150101           Mouse              10
20150202           Mouse              30
20150101           Keyboard           100
20150202           Keyboard           300

how could i do it? Recursive CTE is tied on one running column, like Date, right? Now I have one running column, Date, and another grouping column Item. help me, please!

Pரதீப் · Accepted Answer · 2015-11-19T05:12:13.253

Try using Cross Apply or Correlated sub-query

;with cte as
(
select 
 * from
 (VALUES (20150101,'Mouse',10),
               (20150101,'Keyboard',100),
               (20150202,'Mouse',20),
               (20150202,'Keyboard',200) )tc(Date, Item, BuyItem) 
)
SELECT *
FROM   cte a
       CROSS apply(SELECT Sum(BuyItem) AS running_total
                   FROM   cte b
                   WHERE  a.Item = b.Item
                          AND a.Date >= b.Date) cs

Result:

Date        Item        BuyItem running_total
--------    --------    ------- -------------
20150101    Mouse       10      10
20150202    Mouse       20      30
20150101    Keyboard    100     100
20150202    Keyboard    200     300

Recursive CTE method:

;WITH cte
     AS (SELECT Row_number()OVER(partition BY Item
                    ORDER BY date ) AS rn,*
         FROM   (VALUES (20150101,'Mouse',10),
                        (20150101,'Keyboard',100),
                        (20150202,'Mouse',20),
                        (20150202,'Keyboard',200) )tc(Date, Item, BuyItem)),
     CTE_RunningTotal
     AS (SELECT Date,Item,BuyItem,BuyItem AS running_total,rn
         FROM   cte
         WHERE  rn = 1
         UNION ALL
         SELECT T.Date,T.Item,t.BuyItem,
                T.BuyItem + C.running_total AS running_total,
                t.rn
         FROM   CTE_RunningTotal AS C
                INNER JOIN cte AS T
                        ON T.Item = c.Item
                           AND t.rn = C.rn + 1)
SELECT Date,
       Item,
       BuyItem,
       running_total
FROM   CTE_RunningTotal AS C

Better to update your server to 2012 which can use sum() over(order by) method to calculate running total which much faster than these methods

It worked with sample data. In real world data, I have Date column, two grouping columns (like item and warehouse) and Quantity Column. My table has around 20000 records. These methods take too much time and I have to stop before it gave any result. — Phyo Min Yu, Nov 19 '15 at 06:51

Jenish Rabadiya · Answer 2 · 2015-11-19T06:36:43.510

1

Make a use of windowing function. in SQL 2012+

DECLARE @Items TABLE
(
DATE NVARCHAR(MAX),
Item NVARCHAR(MAX),
BuyItem int
)

INSERT INTO @Items(DATE, Item, BuyItem) VALUES('20150101', 'Mouse', 10)
INSERT INTO @Items(DATE, Item, BuyItem) VALUES('20150101', 'Keyboard', 100)
INSERT INTO @Items(DATE, Item, BuyItem) VALUES('20150202', 'Mouse', 20)
INSERT INTO @Items(DATE, Item, BuyItem) VALUES('20150202', 'Keyboard', 200)

SELECT DATE, Item, SUM(BuyItem) OVER (PARTITION BY Item ORDER BY BuyItem) AS RunningTotal FROM @Items ORDER BY Item DESC

here is output of running above query.

20150101    Mouse       10
20150202    Mouse       30
20150101    Keyboard    100
20150202    Keyboard    300

edited Nov 19 '15 at 06:36

answered Nov 19 '15 at 05:09

Jenish Rabadiya

6,708
6
33
62

anything less than `2012` will not allow `order by` in `Aggregate` with `window` function – Pரதீப் Nov 19 '15 at 05:13
@MM93 Nope above query works perfect even with SQL server 2008. look at [this](http://sqlfiddle.com/#!3/bf52b/2) sql fiddle. – Jenish Rabadiya Nov 19 '15 at 05:33
@Jenish Rabadiya Thank! i tested on sql 2014 and it worked. But, it didn't work on sql 2008r2. – Phyo Min Yu Nov 19 '15 at 06:12
@JenishRabadiya - I guess now you believe what i said – Pரதீப் Nov 19 '15 at 06:26
@MM93 sorry! it does not work with actual server but works with sql fiddle. There must be something wrong with sql fiddle. – Jenish Rabadiya Nov 19 '15 at 06:36

Nguyễn Hải Triều · Answer 3 · 2015-11-19T05:34:21.540

0

In aggregate function, with running total, using Window functions are very good performance

In SQL 2012+:

SELECT *,
   SUM() OVER(PARTITION BY Item, ORDER BY Date) AS RunningTotal
FROM
Your_Table
ORDER BY Item DESC

Faster than when add window frame:

SELECT *,
   SUM() OVER(PARTITION BY Item, ORDER BY Date ROWS UNBOUNDED PRECEDING) AS RunningTotal
FROM
Your_Table
ORDER BY Item DESC

edited Nov 19 '15 at 05:34

answered Nov 19 '15 at 05:10

Nguyễn Hải Triều

1,454
1
8
14

anything less than `2012` will not allow `order by` in `Aggregate` with `window` function – Pரதீப் Nov 19 '15 at 05:13

score 0 · Answer 4 · edited Nov 19 '15 at 09:15

0

SELECT Date,
       item,
       running_total
FROM   #yourtable a
       CROSS apply(SELECT Sum(BuyItem) AS running_total
                   FROM   #yourtable b
                   WHERE  a.Item = b.Item
                          AND a.Date >= b.Date) ca
ORDER  BY BuyItem

edited Nov 19 '15 at 09:15

Kumar Saurabh

2,297
5
29
43

answered Nov 19 '15 at 08:47

Aj.na

283
2
9

Running total on multiple columns

4 Answers4