Field Sum - 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

Field Sum

Author

Topic

Hedges
Starting Member

5 Posts

Posted - 2008-12-22 : 06:21:33

Hello!

New to SQL Server, new to the forum, I need help!

Imagine the following table:

Date____A____B
date1___a1___b1
date2___a2___b2
date3___a3___b3

I'm generating a report like that, until here everything it's done. My problem is how to fill colum "C" that must go like this:

Date____A____B____C
date1___a1___b1___(a1)/(b1)
date2___a2___b2___(a1+a2)/(b1+b2)
date3___a3___b3___(a1+a2+a3)/(b1+b2+b3)
... ... ... ....

The Date, A and B I get directly from one table, my problem is how can I have the total addition from the fields in one colum, not all the fields, all before the line where I am?

Example, imagine that my report has 100 records , at the record 23 I need to know the sum of all A and all B since record 1 until the 23.

Thank you!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-22 : 06:24:57

one solution is this


SELECT t1.date, t1.a, t1.b, (select sum(x.a) / nullif(sum(x.b), 0) from table1 as x where x.date <= t1.date)
FROM Table1 aS t1
order by t1.date

but is not too good for performance. But for 100 records, it will be ok.

E 12°55'05.63"
N 56°04'39.26"

Hedges
Starting Member

5 Posts

Posted - 2008-12-22 : 06:48:46

Thank you!

Well, 100 was one example, it could have more than 1000! But still it doesn't work because I got this message when trying to run it:

The multi-part identifier "t1.date" could not be bound.

No idea... but thank you again!

quote:
Originally posted by Peso

one solution is this

SELECT t1.date, t1.a, t1.b, (select sum(x.a) / nullif(sum(x.b), 0) from table1 as x where x.date <= t1.date)
FROM Table1 aS t1
order by t1.date

but is not too good for performance. But for 100 records, it will be ok.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-22 : 06:51:01

Don't you have a column named "date"?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-22 : 06:54:01

Works perfectly for me.
I think error is about 17 inches in front of screen.

DECLARE	@Sample TABLE
	(
		date DATETIME,
		a MONEY,
		b MONEY
	)

INSERT	@Sample
SELECT	'2008-06-01', 43, 30 UNION ALL
SELECT	'2008-08-01', 17, 25 UNION ALL
SELECT	'2008-10-01', 23, 42

SELECT		*
FROM		@Sample
ORDER BY	date

SELECT		t1.date,
		t1.a,
		t1.b,
		(SELECT SUM(x.a) / NULLIF(SUM(x.b), 0) FROM @Sample AS x WHERE x.date <= t1.date) AS yak
FROM		@Sample AS t1
ORDER BY	t1.date

E 12°55'05.63"
N 56°04'39.26"

Hedges
Starting Member

5 Posts

Posted - 2008-12-22 : 08:24:29

Thank you again!

Yes, I have the field "Date", but I already found the solution for the error, thanks to you!

quote:

Originally posted by Peso

Works perfectly for me.
I think error is about 17 inches in front of screen.

DECLARE	@Sample TABLE
	(
		date DATETIME,
		a MONEY,
		b MONEY
	)

INSERT	@Sample
SELECT	'2008-06-01', 43, 30 UNION ALL
SELECT	'2008-08-01', 17, 25 UNION ALL
SELECT	'2008-10-01', 23, 42

SELECT		*
FROM		@Sample
ORDER BY	date

SELECT		t1.date,
		t1.a,
		t1.b,
		(SELECT SUM(x.a) / NULLIF(SUM(x.b), 0) FROM @Sample AS x WHERE x.date <= t1.date) AS yak
FROM		@Sample AS t1
ORDER BY	t1.date

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-22 : 08:29:59

And what is that solution, so that me and other people might learn?

E 12°55'05.63"
N 56°04'39.26"

Hedges
Starting Member

5 Posts

Posted - 2008-12-22 : 08:44:11

The problem was that I was doing:

SELECT t1.Date
,t1.A
,t1.B
,t2.total

FROM [mytable] as t1

(select sum(A)/sum(B) as total
from [mytable]
where Date <= t1.Date) as t2

Etc...

And as you told me it should be:

SELECT t1.Date
,t1.A
,t1.B
,(select sum(A)/sum(B) as total
from [mytable]
where Date <= t1.Date)
FROM [mytable] as t1

etc

this was the cause of the error, now it runs but I can't tell if it works or not because the table stills empty, probably in a couple of hours I will have data on the table, and then I will post if it works or not, and if not, if I found another solution I will post it for sure!

Thanks

quote:
Originally posted by Peso

And what is that solution, so that me and other people might learn?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-22 : 08:45:53

There are another method that is lightning fast if the number of records are more than a few hundred.
Let us now if that happens.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-22 : 08:50:20

[code]DECLARE @Sample TABLE
(
date DATETIME PRIMARY KEY CLUSTERED,
a MONEY,
b MONEY,
d MONEY
)

INSERT @Sample (date, a, b)
SELECT '2008-06-01', 43, 30 UNION ALL
SELECT '2008-08-01', 17, 25 UNION ALL
SELECT '2008-10-01', 23, 42

-- Very fast cumulative update!
DECLARE @a MONEY,
@b MONEY

UPDATE @Sample
SET @a = a + coalesce(@a, 0),
@b = b + coalesce(@b, 0),
d = @a / nullif(@b, 0)

-- Display the result
SELECT date,
a,
b,
d
FROM @Sample
ORDER BY date[/code]

E 12°55'05.63"
N 56°04'39.26"

raky
Aged Yak Warrior

767 Posts

Posted - 2008-12-22 : 10:08:30

Hi,

Please check u r query with these values

DECLARE @Sample TABLE
(
date DATETIME PRIMARY KEY CLUSTERED,
a MONEY,
b MONEY,
d MONEY
)

INSERT @Sample (date, a, b)
SELECT '2008-06-01', 43, 30 UNION ALL
SELECT '2008-08-01', 17, 25 UNION ALL
SELECT '2008-10-01', 23, null

It is returning wrong values...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-22 : 10:13:24

Oh, NULL is an accepted value?
Use COALESCE if that's the case.

SELECT		t1.date,
		t1.a,
		t1.b,
		(SELECT SUM(COALESCE(x.a, 0)) / NULLIF(SUM(COALESCE(x.b, 0)), 0) FROM @Sample AS x WHERE x.date <= t1.date) AS yak
FROM		@Sample AS t1
ORDER BY	t1.date

UPDATE	@Sample
SET	@a = coalesce(a, 0) + coalesce(@a, 0),
	@b = coalesce(b, 0) + coalesce(@b, 0),
	d = @a / nullif(@b, 0)

E 12°55'05.63"
N 56°04'39.26"

Hedges
Starting Member

5 Posts

Posted - 2008-12-23 : 10:08:50

Hello again,

Just to say that it worked!

Thanks for the help!

quote:

Originally posted by Peso

Oh, NULL is an accepted value?
Use COALESCE if that's the case.

SELECT		t1.date,
		t1.a,
		t1.b,
		(SELECT SUM(COALESCE(x.a, 0)) / NULLIF(SUM(COALESCE(x.b, 0)), 0) FROM @Sample AS x WHERE x.date <= t1.date) AS yak
FROM		@Sample AS t1
ORDER BY	t1.date

UPDATE	@Sample
SET	@a = coalesce(a, 0) + coalesce(@a, 0),
	@b = coalesce(b, 0) + coalesce(@b, 0),
	d = @a / nullif(@b, 0)

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources