Running totals when grouping is character

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Running totals when grouping is character

Author

Topic

Rosembm1
Starting Member

15 Posts

Posted - 2010-01-04 : 08:24:02

I am trying to create a running total by character group:
The table setup is below:
create table #Temp
(
GroupA varchar(255)
,GroupB varchar(255)
,TotalB smallint
)

insert into #temp VALUES ('Hip','Path1', 2)
insert into #temp VALUES ('Hip','Path2', 2)
insert into #temp VALUES ('Hip','Path3 ', 2)
insert into #temp VALUES ('Hip','Path4', 1)
insert into #temp VALUES ('Hip','Path5', 1)

insert into #temp VALUES ('Knee', 'Path1', 4)
insert into #temp VALUES ('Knee', 'Path2', 3)
insert into #temp VALUES ('Knee' , 'Path3', 3)
insert into #temp VALUES ('Knee', 'Path4', 3)
insert into #temp VALUES ('Knee', 'Path5', 1)
insert into #temp VALUES ('Knee' , 'Path6', 1)
insert into #temp VALUES ('Knee', 'Path7', 1)
insert into #temp VALUES ('Knee', 'Path8', 1)
insert into #temp VALUES ('Knee' ,'Path9', 1)

The output I am seeking is in the far right column
GroupA GroupB TotalB Running_Total
Hip Procedure Path1 2 2
Hip Procedure Path2 2 4
Hip Procedure Path3 2 6
Hip Procedure Path4 1 7
Hip Procedure Path5 1 8
Knee Procedure Path1 4 4
Knee Procedure Path2 3 7
Knee Procedure Path3 3 10
Knee Procedure Path4 3 13
Knee Procedure Path5 1 14
Knee Procedure Path6 1 15
Knee Procedure Path7 1 16
Knee Procedure Path8 1 17
Knee Procedure Path9 1 18

Thanks for your help!

MWRosenblatt
Spins Yak

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-04 : 08:51:26

Which version of SQL Server?

No, you're never too old to Yak'n'Roll if you're too young to die.

Edit: Ignore my question. I thought about sum() over (partition by...) but that isn't the answer...sorry.

Look here:
http://www.sqlteam.com/article/calculating-running-totals
and here:
http://weblogs.sqlteam.com/mladenp/archive/2009/07/28/SQL-Server-2005-Fast-Running-Totals.aspx

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-04 : 09:27:52

<<
sum() over (partition by...)
>>

That was not yet supported but very useful

Madhivanan

Failing to plan is Planning to fail

Rosembm1
Starting Member

15 Posts

Posted - 2010-01-04 : 11:00:52

We are currently using SQL server 2005. The first thing I had tried the sum over partition by and I could not get it to work.

MWRosenblatt
Spins Yak

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-04 : 11:12:50

Try this..

SELECT a.GroupA,
       a.GroupB,
       SUM(b.TotalB) AS [RunningTotal]
FROM (select row_number() over(partition by GroupA order by GroupB) as seq,* from #temp) a
CROSS JOIN (select row_number() over(partition by GroupA order by GroupB) as seq,* from #temp) b
WHERE (b.seq <= a.seq and b.GroupA = a.GroupA) 
GROUP BY a.GroupA,a.GroupB
ORDER BY a.GroupA,a.GroupB

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-04 : 11:13:36

Or this...whichever is faster

SELECT GroupA,
       GroupB,
       TotalB+COALESCE((SELECT SUM(TotalB) 
                      FROM (select row_number() over(partition by GroupA order by GroupB) as seq,* from #temp) b 
                      WHERE b.seq < a.seq and a.GroupA = b.GroupA),0)
                         AS RunningTotal
FROM (select row_number() over(partition by GroupA order by GroupB) as seq,* from #temp) a
ORDER BY GroupA,GroupB

Rosembm1
Starting Member

15 Posts

Posted - 2010-01-04 : 12:57:22

Thanks! M

MWRosenblatt
Spins Yak

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-04 : 13:01:26

You're welcome.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-05 : 01:16:14

or look at the method under running total
http://sqlblogcasts.com/blogs/madhivanan/archive/2009/06/10/quirky-update-in-sql-server.aspx

Madhivanan

Failing to plan is Planning to fail

Sachin.Nand

2937 Posts

Posted - 2010-01-05 : 02:19:18

quote:
Originally posted by madhivanan

or look at the method under running total
http://sqlblogcasts.com/blogs/madhivanan/archive/2009/06/10/quirky-update-in-sql-server.aspx

Madhivanan

Failing to plan is Planning to fail

But the OP wants group wise running totals.

PBUH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-05 : 03:54:59

quote:
Originally posted by Idera

quote:
Originally posted by madhivanan

or look at the method under running total
http://sqlblogcasts.com/blogs/madhivanan/archive/2009/06/10/quirky-update-in-sql-server.aspx

Madhivanan

Failing to plan is Planning to fail

But the OP wants group wise running totals.

PBUH

You can change that method little bit
See Reset Serial no method

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources