sum running totals - SQL Server Forums

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)

sum running totals

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2008-02-05 : 05:15:36

Hey i've an sales value that i sum up to give me the weekly sales, i would also like to keep a running total for sales year to date by week. So if the sales for week 1 was 500 and sales for week 2 was 600 the running total for week one and 2 would look like this

week Sales Sales total by week
1 500 500
2 600 1100

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-05 : 05:23:24

can you post table structure with some sample data?

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-02-05 : 05:35:28

Is this you are looking for:

Declare @Table1 table
(
id int IDENTITY(1,1),
sales int
)
Insert @Table1
select 500 union all
select 600 union all
select 200 union all
select 100

select sales ,(select SUM(sales) from @table1 t2 where t2.id<=t1.id) as [Running Total ]from @table1 t1

I would like some one to provide me above result using Sql 2005.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-02-05 : 05:39:26

Declare @t table(Week int,Amount int)

Insert into @t
select 1,200 union all
select 2,200 union all
select 3,300 union all
select 4,400

select Week,Amount,TotalSales=(select sum(Amount) from @t T2 where T2.weekid<=T1.Weekid) from @t T1

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2008-02-05 : 06:04:08

Here is my table structure.

CREATE TABLE [dbo].[Sales_Data](
[Branch] [varchar] (10),
[Region] [varchar](20),
[week_no] [tinyint] NOT NULL,
[year] [smallint] NOT NULL,
[sales_weekly] [money] NULL,
[running_total_sales] [money] null
) ON [PRIMARY]

Branch Region week_no year sales_weekly running_total_sales
A ire 1 2008 100 100
A ire 2 2008 200 300

i'd like to try and do the calculation of the colum running_total in a view as am using the view in a SSIS lookup

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-05 : 06:14:10

[code]SELECT s.[Branch],
s.[Region],
s.[week_no],
s.[year],
s.[sales_weekly],
tmp.[RunningTotal]
FROM Sales_Data s
CROSS APPLY (SELECT SUM([sales_weekly]) AS RunningTotal
WHERE [Branch]=s.[Branch]
AND [Region]=s.[Region]
AND [year]=s.[year]
AND week_no<=s.[week_no])tmp
[/code]

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2008-02-05 : 06:35:27

Am getting this error message now. After trying visakh16 code

"Aggregates on the right side of an APPLY cannot reference columns from the left side."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-05 : 06:41:22

quote:
Originally posted by rookie_sql

Am getting this error message now. After trying visakh16 code

"Aggregates on the right side of an APPLY cannot reference columns from the left side."

sorry i missed some part while posting

SELECT s.[Branch], 
s.[Region],
s.[week_no],
s.[year],
s.[sales_weekly],
tmp.[RunningTotal] 
FROM Sales_Data s
CROSS APPLY (SELECT  SUM([sales_weekly]) AS RunningTotal
            FROM Sales_Data
            WHERE [Branch]=s.[Branch] 
             AND [Region]=s.[Region]
             AND [year]=s.[year]
             AND week_no<=s.[week_no])tmp

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-02-05 : 06:42:00

SELECT s.[Branch],
s.[Region],
s.[week_no],
s.[year],
s.[sales_weekly],
tmp.[RunningTotal]
FROM Sales_Data s
CROSS APPLY (SELECT SUM([sales_weekly]) AS RunningTotal from Sales_Data WHERE [Branch]=s.[Branch]
AND [Region]=s.[Region]
AND [year]=s.[year]
AND week_no<=s.[week_no])tmp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-05 : 08:21:17

If you want to show data in Reports, then make use of it's Running Total feature

Madhivanan

Failing to plan is Planning to fail

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2008-02-05 : 10:36:18

Thanks for your help on this i got it to work. I have never used CROSS APPLY before so it was good to learn this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources