TSQL-2008 SUM(X) OVER (PARTITION ... ORDER BY CLAUSE)

Question

I really need to take what I have as a result of a CTE, and calculate the cummulative value of groups of data.

The dataset is:

PERIOD  FT  GROUP   DEPT    VALUE
1   Actual  KINDER  MATH    200
2   Actual  KINDER  MATH    363
3   Actual  KINDER  MATH    366
1   Budget  KINDER  MATH    457
2   Budget  KINDER  MATH    60
3   Budget  KINDER  MATH    158
1   Actual  HIGHSCH ENGLISH 456
2   Actual  HIGHSCH ENGLISH 745
3   Actual  HIGHSCH ENGLISH 125
1   Budget  HIGHSCH ENGLISH 364
2   Budget  HIGHSCH ENGLISH 158
3   Budget  HIGHSCH ENGLISH 200
6   Budget  HIGHSCH ENGLISH 502
7   Budget  HIGHSCH ENGLISH 650
1   Actual  COLL    ENGLISH 700
2   Actual  COLL    ENGLISH 540
3   Actual  COLL    ENGLISH 160
1   Budget  COLL    ENGLISH 820
2   Budget  COLL    ENGLISH 630
3   Budget  COLL    ENGLISH 800

What I want is an additional column that holds the cummulative amount for each FT, Group, Dept.

So basically, I would like it to look like this:

PERIOD  FT  GROUP   DEPT    VALUE   ACC VALUE   
1   Actual  KINDER  MATH    200 200 
2   Actual  KINDER  MATH    363 563 
3   Actual  KINDER  MATH    366 929 
1   Budget  KINDER  MATH    457 457 
2   Budget  KINDER  MATH    60  517 
3   Budget  KINDER  MATH    158 675 
1   Actual  HIGHSCH ENGLISH 456 456 
2   Actual  HIGHSCH ENGLISH 745 1201    
3   Actual  HIGHSCH ENGLISH 125 1326    
1   Budget  HIGHSCH ENGLISH 364 364 
2   Budget  HIGHSCH ENGLISH 158 522 
3   Budget  HIGHSCH ENGLISH 200 722 
1   Budget  HIGHSCH ENGLISH 502 502 
2   Budget  HIGHSCH ENGLISH 650 1152    
3   Budget  HIGHSCH ENGLISH 336 1488    
1   Actual  COLL    ENGLISH 700 700 
2   Actual  COLL    ENGLISH 540 1240    
3   Actual  COLL    ENGLISH 160 1400    
1   Budget  COLL    ENGLISH 820 820 
2   Budget  COLL    ENGLISH 630 1450    
3   Budget  COLL    ENGLISH 800 2250

If I was in SQL 2012, I'd use something similar to this:

SELECT   period
    ,ft
    ,group
    ,dept
    ,value
    ,CASE
        WHEN FT = 'Actual' THEN SUM(value) OVER (PARTITION BY dept, group, ft ORDER BY period)
        ELSE value
        END AS AccValue 
FROM myTable

However, I am on 2008 and can't use this method and am stumped on how to replicate this for my data.

Please can anyone help?

Why can't you replicate this on 2008? `OVER` has been around since 2005. — Kermit, Oct 30 '13 at 14:02
Not just "over"...OVER..PARTITION BY and ORDER BY on an aggregate. I tried it and each time got an error on the order by. After looking online it says it isnt a feature of 2008. — Uchenna Ebilah, Oct 30 '13 at 14:04
@FreshPrinceOfSO - The `ORDER BY` in an aggregate for running totals etc is 2012+ — Martin Smith, Oct 30 '13 at 14:05
I do have an idea to make this work but it involves creating another column with numbers identifying the group. With this I can use this feature without needing the orderby — Uchenna Ebilah, Oct 30 '13 at 14:10
This may be uglier than you had in mind, but would definitely work: JOIN to a subquery of MyTable with a `GROUP BY dept, group, ft`, and with the subquery returning 3 SUM columns... two of which are SUMs of CASE statements: `CASE WHEN PERIOD > 1 THEN 0` on one column, and `CASE WHEN PERIOD > 2 THEN 0` on another column. If this is not too ugly for you, then I'll post code as an answer. — Doug_Ivison, Oct 30 '13 at 14:26

rwking · Answer 1 · 2013-10-30T16:14:36.070

3

Simple INNER JOIN should do the trick. Unless I'm misunderstanding you, what you want is a running total, correct?

This example creates a dummy table with dummy data, then uses an inner join for the running total. From a performance standpoint, the Common Table Expression is likely more efficient. But for simplicity, the inner join my be preferential.

/* Dummy table */    

create table testing1
(col1 int not null identity(1,1),
col2 varchar(5),
col3 int)


insert into testing1
values ('a', 10), ('a', 20), ('a', 30), ('b', 40), ('b', 50)

/* Running total example */

SELECT a.col1
           , a.col2
           , a.col3
           , SUM(b.col3) AS total

FROM testing1 a INNER JOIN testing1 b
     ON  a.col1 >= b.col1
     AND a.col2 = b.col2

GROUP BY a.col1, a.col2, a.col3
ORDER BY a.col1



/* Edit to include Output */
col1    col2    col3    total
1   a   10  10
2   a   20  30
3   a   30  60
4   b   40  40
5   b   50  90

edited Oct 30 '13 at 16:14

answered Oct 30 '13 at 15:00

rwking

1,032
6
18

Sweet... better than my comment ;) – Doug_Ivison Oct 30 '13 at 15:29
This didn't work. Can you maybe show your output using http://sqlfiddle.com just in case I'm wrong x – Uchenna Ebilah Oct 30 '13 at 15:43
1

Try it again. If you copied and pasted, there was a random " ' " that needed to be removed. I have deleted it. Also, sorry for the funky column formatting on my output, but you can see it did a running total. – rwking Oct 30 '13 at 16:15
1

Thanks a lot RWKing for reposting it. I've just tried applying it to my code/data and it's taking ages to run and complete. Do you have a solutions you could walk me through to make it more efficient for large amounts of data? – Uchenna Ebilah Oct 31 '13 at 10:55
1

(1) Test dropping the inequality in the JOIN -- the `a.col1 >= b.col1` -- temporarily taking it out (even though it will make your numbers temporarily incorrect). If much faster, move the inequality to the SUM: `SUM(CASE WHEN (a.col1 >= b.col1) THEN b.col3 ELSE 0 END) AS total`. (2) OFTEN SURPRISINGLY EFFECTIVE: breaking into two SELECTs ((either a SELECT...INTO followed by a SELECT, or maybe subqueries)) where the *first* to execute is the SUM...GROUP BY, and the *second* SELECT joins that to the detail. (3) Test without the ORDER BY... if much faster, try moving it to a second SELECT. – Doug_Ivison Oct 31 '13 at 14:33
P.S. adding cadaver's index suggestion, below, to my point (2) above: two separate SELECTs can speed things up a lot... even more so, if the table produced by the first SELECT...INTO *also* gets indexed. – Doug_Ivison Oct 31 '13 at 15:19

score 1 · Answer 2 · answered Oct 30 '13 at 14:51

1

This should work. (I changed a forbidden column name from "GROUP" to "GROUP_")

with cte (PERIOD,  FT,  GROUP_,   DEPT,   VALUE, AccValue) as (select t1.PERIOD,  t1.FT,  t1.GROUP_,   t1.DEPT,    t1.VALUE, SUM(t2.VALUE)   as AccValue
                            from            myTable t1
                            join myTable t2 on t1.PERIOD>= t2.PERIOD
                            and  t1.FT =  t2.FT 
                            and t1.GROUP_ = t2.GROUP_           
                            and t1.DEPT = t2.DEPT
                            group by    t1.PERIOD,  t1.FT,  t1.GROUP_,   t1.DEPT,    t1.VALUE
                    )
                                select  PERIOD
                    ,FT
                    ,GROUP_
                    ,DEPT
                    ,VALUE
                    ,CASE
                        WHEN FT = 'Actual' THEN AccValue
                        ELSE VALUE
                        END AS AccValue 
                        from cte
                                order by GROUP_ desc,  FT, PERIOD, DEPT desc,    VALUE

answered Oct 30 '13 at 14:51

cadaver

245
2
11

Thanks. I've tried to run this and so far with my data set it's taking over a minute to run, so I feel it wont be a viable solution. :( Any ideas on a more efficient less costly way? – Uchenna Ebilah Oct 30 '13 at 15:53
If you can use a temporary table instead of CTE, and make index on all columns besides VALUE. – cadaver Oct 30 '13 at 18:38
Okay. I understand how to make a temporary table and will try that. I'm not sure on how to do the other half of what you've suggested. – Uchenna Ebilah Oct 31 '13 at 10:39
1

create clustered index id on myTable(FT, GROUP_, DEPT, PERIOD) – cadaver Oct 31 '13 at 13:29
**START** with cadaver's index suggestion, above ^^^ – Doug_Ivison Oct 31 '13 at 15:17

TSQL-2008 SUM(X) OVER (PARTITION ... ORDER BY CLAUSE)

2 Answers2

Linked