How to get Running total in SQL when values are not same?

Question

I am trying to get the running total for Week (like Show as 'Running Total in' in Excel Pivot table.

I have tried with the below query.

select A.*,
(SELECT SUM(b.value)
            FROM Tbl_Running_Total_Test B
            WHERE   B.Fweek <= A.Fweek
                    AND A.level1=B.level1
                    AND A.level2=B.level2
                    AND A.level3=B.level3
                    AND A.level4=B.level4
            )
 from Tbl_Running_Total_Test A

Here is a SQL Fiddle: SQl Fiddle Sample table

If I use the above query I am not getting the expected result.

James Z · Accepted Answer · 2016-08-02 13:28:25Z

2

Your level3 & level4 have extra characters, so that's why it's not adding up:

(4,'1QAB','2XMN','3YOPq','4ZGHi',25),
(4,'1QAc','2XMo','3YOqr','4ZGij',74),
(4,'1QAd','2XMp','3YOrs','4ZGjk',86),
(4,'1QAe','2XMq','3YOst','4ZGkl',97);

The others are like this:

(3,'1QAB','2XMN','3YOP','4ZGH',40),
(3,'1QAc','2XMo','3YOq','4ZGi',60),
(3,'1QAd','2XMp','3YOr','4ZGj',80),
(3,'1QAe','2XMq','3YOs','4ZGk',120),

answered Aug 2, 2016 at 13:28

James Z

12.3k10 gold badges27 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Punith Over a year ago

Agreed, Is there anyway to get the desired result when looking out the values for level1 or Level2

James Z Over a year ago

@Punith Well that depends on your requirement. Strip away the last characters, don't match the level at all...

Collectives™ on Stack Overflow

How to get Running total in SQL when values are not same?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related