Keep running total during query

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

SQL Server 2005 Forums

Transact-SQL (2005)

Keep running total during query

Author

Topic

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2008-10-17 : 15:25:11

Hi,

I have a view that returns a date column plus a column for the number of widgets that have been sold on the date. How can I make the query add the total widgets sold before a given date be added to the current date?

So...

1/1/08 - 20
2/1/08 - 30
3/1/08 - 40

...becomes...

1/1/08 - 20
2/1/08 - 50
3/1/08 - 90

A sub-query that adds up all widgets sold before the current date seems horribly inefficient.

Cheers, XF.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-17 : 16:01:43

[code]select col1, (
select sum(col2)
from tabl a, tabl b
where a.col1 <= b.col1
and b.col1 = c.col1
) as sumcol2
from tabl c[/code]

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2008-10-17 : 16:42:55

ok, I guess a sub-qeury it is.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-17 : 17:24:45

[code]declare @sample table
(
id int identity(1,1) not null,
col1 datetime,
col2 int
)

insert into @sample (col1, col2)
select '1/1/08',20
union
select '2/1/08',30
union
select '3/1/08',40

select a.col1, col2, (select sum(col2) from @sample where id <=a.id)
from @sample a

col1 col2
------------------------------------------------------ ----------- -----------
2008-01-01 00:00:00.000 20 20
2008-02-01 00:00:00.000 30 50
2008-03-01 00:00:00.000 40 90

(3 row(s) affected)

[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-18 : 08:29:52

quote:
Originally posted by X-Factor

Hi,

I have a view that returns a date column plus a column for the number of widgets that have been sold on the date. How can I make the query add the total widgets sold before a given date be added to the current date?

So...

1/1/08 - 20
2/1/08 - 30
3/1/08 - 40

...becomes...

1/1/08 - 20
2/1/08 - 50
3/1/08 - 90

A sub-query that adds up all widgets sold before the current date seems horribly inefficient.

Cheers, XF.

Where do you want to show running total?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-18 : 09:59:51

Another 2005 approach

SELECT date,widgetno,widgetno+COLAESCE(tmp.prevsum,0) AS runningtotal
FROM table t
OUTER APPLY(SELECT SUM(widgetno) AS prevsum
            FROM Table
            WHERE date <t.date)tmp
ORDER BY date

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources