[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
6
Medium Priority
?
292 Views
Last Modified: 2014-07-24
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
Comment
Question by:codeoxygen
  • 3
  • 2
6 Comments
 
LVL 143

Accepted Solution

by:
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

Open in new window


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

Open in new window

0
 

Author Closing Comment

by:codeoxygen
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

by:PortletPaul
ID: 40216697
are you using MS SQL 2012 or later?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:codeoxygen
ID: 40216698
2008
0
 

Author Comment

by:codeoxygen
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

by:Guy Hengel [angelIII / a3]
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

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

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question