Running Total with Average - 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

General SQL Server Forums

New to SQL Server Programming

Running Total with Average

Author

Topic

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-08-11 : 07:26:13

Hi,

I am using the 'running total' article from this website to accumulate a value. in the next step I would like to sum up the product of two values of each line and divide it by the accumulated value, but it won't work:

DECLARE @Mult INT
SELECT a.DayCount, a.Sales, SUM(b.Sales), @Mult AS (a.DayCount * a.Sales), (SUM(@Mult)/SUM(b.Sales)) AS AverageVal
FROM Sales a
CROSS JOIN Sales b
WHERE (b.DayCount <= a.DayCount) AS RunningTotal
GROUP BY a.DayCount,a.Sales
ORDER BY a.DayCount,a.Sales

you guys are great, thanks in advance!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 08:08:45

what's the error you got? or is it that you're notgetting expected values? illustrate with some data the problem you face.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 08:10:58

A lot of errors.
You can't select and display values from a SELECT query and simultaneously assign values to a variable.

E 12°55'05.25"
N 56°04'39.16"

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-08-11 : 08:23:45

tnx for the answer! how else can I calculate the product (@Mult) row by row and accumulate it, to use it for calculating the weighted average?

it should look something like this (maybe Daycount isn't an appropriate Name, but this is just an example):

Daycount....Sales.....AccumulatedSales......WeightedAvg
1...........100.......100...................1 -> (1*100)/100
2...........500.......600...................1,83 ->((1*100)+(2*500))/600

So I need to create a variable, which calculates the product of Daycount*Sales ((1*100),(2*500)....)of each row. afterwards I need to accumulate this variable like I did it with the sales..

tnx again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 08:25:25

are you using sql 2005?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 08:25:44

See http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 08:35:07

[code]-- Prepare sample data
CREATE TABLE #Sample
(
DayCount INT,
Sales INT,
AccumulatedSales INT,
WeightedAvg MONEY
)

CREATE CLUSTERED INDEX IX_Sample ON #Sample (DayCount)

INSERT #Sample
(
DayCount,
Sales
)
SELECT 1, 100 UNION ALL
SELECT 2, 500

-- Do the "Running total"
DECLARE @AccSales INT

UPDATE s
SET @AccSales = AccumulatedSales = Sales + COALESCE(@AccSales, 0)
FROM #Sample AS s WITH (INDEX (IX_Sample))

-- Do the "Weighted Average"
DECLARE @Sales MONEY

UPDATE s
SET @Sales = DayCount * Sales + COALESCE(@Sales, 0),
WeightedAvg = @Sales / AccumulatedSales
FROM #Sample AS s WITH (INDEX (IX_Sample))

-- Show the result
SELECT *
FROM #Sample

-- Clean up
DROP TABLE #Sample[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 08:36:56

Or the UPDATE's in one step

-- Prepare sample data
CREATE TABLE	#Sample
		(
			DayCount INT,
			Sales INT,
			AccumulatedSales INT,
			WeightedAvg MONEY
		)

CREATE CLUSTERED INDEX IX_Sample ON #Sample (DayCount)

INSERT	#Sample
	(
		DayCount,
		Sales
	)
SELECT	1, 100 UNION ALL
SELECT	2, 500

-- Do the calculation
DECLARE	@Sales MONEY,
	@AccSales INT

UPDATE	s
SET	@Sales = DayCount * Sales + COALESCE(@Sales, 0),
	@AccSales = AccumulatedSales = Sales + COALESCE(@AccSales, 0),
	WeightedAvg = @Sales / @AccSales
FROM	#Sample AS s WITH (INDEX (IX_Sample))

-- Show the result
SELECT	*
FROM	#Sample

-- Clean up
DROP TABLE	#Sample

E 12°55'05.25"
N 56°04'39.16"

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-08-11 : 09:12:14

isn't there a more simple way to just add something to the existing code :(

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 09:15:05

Yes, there is if you can accept VERY long execution times due to the RBAR (triangular join) query it will require.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 09:18:35

[code]DECLARE @Sample TABLE
(
DayCount INT,
Sales INT
)

INSERT @Sample
SELECT 1, 100 UNION ALL
SELECT 2, 500

SELECT DayCount,
Sales,
AccumulatedSales,
1.0 * WeightedAvg / AccumulatedSales AS WeightedAvg
FROM (
SELECT x.DayCount,
x.Sales,
(SELECT SUM(r.Sales) FROM @Sample AS r WHERE r.DayCount <= x.DayCount) AS AccumulatedSales,
(SELECT SUM(e.DayCount * e.Sales) FROM @Sample AS e WHERE e.DayCount <= x.DayCount) AS WeightedAvg
FROM @Sample AS x
) AS d[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 09:21:19

[code]DECLARE @Sample TABLE
(
DayCount INT,
Sales INT
)

INSERT @Sample
SELECT 1, 100 UNION ALL
SELECT 2, 500

SELECT s.DayCount,
s.Sales,
SUM(x.Sales) AS AccumulatedSales,
1.0 * SUM(x.DayCount * x.Sales) / SUM(x.Sales) AS WeightedAvg
FROM @Sample AS s
INNER JOIN @Sample AS x ON x.DayCount <= s.DayCount
GROUP BY s.DayCount,
s.Sales[/code]

E 12°55'05.25"
N 56°04'39.16"

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-08-11 : 10:14:57

awesome, it works!!

how come it didn't work without the 1.0 in front of the sum of the product??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 10:18:14

Learn about INTEGER division.
And don't come back in a few weeks when the query is taking a really long time to exetue.

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 10:18:51

quote:
Originally posted by rocco2008

awesome, it works!!

how come it didn't work without the 1.0 in front of the sum of the product??

its because of implicit conversion. as SUM() functions returntype is integer it implicitly casts result to int so you wont get decimal part. more details below

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/01/16/beware-of-implicit-conversions.aspx

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-08-18 : 08:56:51

hi, I am now using this version which works great, but how do I update an exisiting column (AccumulatedSales, WieghtedAvg) now only filled with "0" values, with the real calculated values, rather than just getting them printed out?

SELECT s.DayCount,
s.Sales,
SUM(x.Sales) AS AccumulatedSales,
1.0 * SUM(x.DayCount * x.Sales) / SUM(x.Sales) AS WeightedAvg
FROM @Sample AS s
INNER JOIN @Sample AS x ON x.DayCount <= s.DayCount
GROUP BY s.DayCount,
s.Sales

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-18 : 09:34:37

[code]UPDATE s
SET s.AccumulatedSales=(SELECT SUM(x.Sales) FROM @Sample WHERE DayCount <= s.DayCount),
s.WeightedAvg=(SELECT 1.0 * SUM(x.DayCount * x.Sales) / SUM(x.Sales) FROM @Sample WHERE DayCount <= s.DayCount)
FROM @Sample s[/code]

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-08-18 : 09:58:40

tnx for the answer, I get an error saying: "Incorrect Syntax near the Keyword 'GROUP'"

My code looks like this:

UPDATE s
SET s.AccumulatedSales=(SELECT SUM(x.Sales) FROM @Sample WHERE DayCount <= s.DayCount),
s.WeightedAvg=(SELECT 1.0 * SUM(x.DayCount * x.Sales) / SUM(x.Sales) FROM @Sample WHERE DayCount <= s.DayCount)
FROM @Sample s
CROSS JOIN @Sample AS x ON x.DayCount <= s.DayCount
GROUP BY s.DayCount,
s.Sales

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-18 : 10:04:22

quote:
Originally posted by rocco2008

tnx for the answer, I get an error saying: "Incorrect Syntax near the Keyword 'GROUP'"

My code looks like this:

UPDATE s
SET s.AccumulatedSales=(SELECT SUM(x.Sales) FROM @Sample WHERE DayCount <= s.DayCount),
s.WeightedAvg=(SELECT 1.0 * SUM(x.DayCount * x.Sales) / SUM(x.Sales) FROM @Sample WHERE DayCount <= s.DayCount)
FROM @Sample s
~~CROSS JOIN @Sample AS x ON x.DayCount <= s.DayCount~~
GROUP BY s.DayCount,
s.Sales

remove it

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-08-18 : 10:17:07

now this happens:

Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'x' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'x' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'x' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'x' does not match with a table name or alias name used in the query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-18 : 10:21:53

ah...remove them too


UPDATE s
SET s.AccumulatedSales=(SELECT SUM(Sales) FROM @Sample WHERE DayCount <= s.DayCount),
s.WeightedAvg=(SELECT 1.0 * SUM(DayCount * Sales) / SUM(Sales) FROM @Sample WHERE DayCount <= s.DayCount)
FROM @Sample s

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources