[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Not able to Calculate Running Total in sql server

Posted on 2014-07-24
Medium Priority
292 Views
my table looks like as bellow

ID             Person          bal     N0          running  Sum
1                  1                 5         1                5
2                   2                5          2              10
3                   2                5          3              10
4                   3                10        4               20
5                   4                6          5                26
6                   4                6          6                26
7                    5                1         7                27

here i want Running total based on distinct value   Person column   and bal column  value  sum

if person column  value is reapting at that at that time bal value should be added to running total
0
Question by:codeoxygen
• 3
• 2

LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 40216631
check out this script:
``````declare @t table (id int , person int, bal int, no int )
insert into @t values(1,                  1                , 5        , 1      )
insert into @t values(2,                   2               , 5        ,  2     )
insert into @t values(3,                   2               , 5        ,  3      )
insert into @t values(4,                   3               , 10       , 4        )
insert into @t values(5,                   4               , 6        ,  5        )
insert into @t values(6,                   4               , 6        ,  6         )
insert into @t values(7,                    5              ,  1       ,  7          )

select t.*
, (select sum(b.bal)
from @t b
where b.id <= t.id
and not exists(select null from @t o
where o.person = b.person
and o.bal = b.bal
and o.id < b.id
)
) running
from @t t
``````

output:
``````id          person      bal         no          running
----------- ----------- ----------- ----------- -----------
1           1           5           1           5
2           2           5           2           10
3           2           5           3           10
4           3           10          4           20
5           4           6           5           26
6           4           6           6           26
7           5           1           7           27
``````
0

Author Closing Comment

ID: 40216694
If i have to calculate similarly for 3 more coulumns based on the person column ,

should i repeat the  subqurey  3 times or any other bettter way
0

LVL 49

Expert Comment

ID: 40216697
are you using MS SQL 2012 or later?
0

Author Comment

ID: 40216698
2008
0

Author Comment

ID: 40216703
ID             Person          bal     N0          running  Sum     Fine             fine running sum

1                  1                 5         1                5                          2                      2
2                   2                5          2              10                         3                      5
3                   2                5          3              10                         3                      5
4                   3                10        4               20                        1                      6
5                   4                6          5                26                       2                      8
6                   4                6          6                26                       2                      8
7                    5                1         7                27                       5                      13
0

LVL 143

Expert Comment

ID: 40216716
>should i repeat the  subqurey  3 times or any other bettter way
you will have to repear the subquery 3 times when using this technique.
with a CTE expression, we could do intermediate steps (to perform the "distinct" in a CTE expression first), but still we will need a subquery for each expression./
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this â€¦
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Acâ€¦
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
###### Suggested Courses
Course of the Month17 days, 23 hours left to enroll