calculate 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

calculate sum

Author

Topic

maevr
Posting Yak Master

169 Posts

Posted - 2009-06-17 : 01:13:49

I have a problem calculate the sum of zvalue1 * xvalue2 for every id grouped by wallnr.

create table #table1
(
id int,
wallnr int not null,
zvalue1 int not null,
xvalue2 int not null,
)

insert into #table1(id,wallnr,zvalue1,xvalue2) values(1,1,50,100)
insert into #table1(id,wallnr,zvalue1,xvalue2) values(1,2,150,500)
insert into #table1(id,wallnr,zvalue1,xvalue2) values(2,1,500,1000)
insert into #table1(id,wallnr,zvalue1,xvalue2) values(3,1,250,123)
insert into #table1(id,wallnr,zvalue1,xvalue2) values(3,2,5,10)
insert into #table1(id,wallnr,zvalue1,xvalue2) values(3,3,90,100)

expected output:
for every id do the following calculation, for example id = 1:
ID
id=1: (50 * 100) + (150 * 500)
id=2: (500 * 1000)
id=3: (250 * 5) + (5 * 10) + (90 * 100)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-17 : 01:41:13

create table #table1
(
id int,
wallnr int not null,
zvalue1 int not null,
xvalue2 int not null,
)

insert into #table1(id,wallnr,zvalue1,xvalue2) values(1,1,50,100)
insert into #table1(id,wallnr,zvalue1,xvalue2) values(1,2,150,500)
insert into #table1(id,wallnr,zvalue1,xvalue2) values(2,1,500,1000)
insert into #table1(id,wallnr,zvalue1,xvalue2) values(3,1,250,123)
insert into #table1(id,wallnr,zvalue1,xvalue2) values(3,2,5,10)
insert into #table1(id,wallnr,zvalue1,xvalue2) values(3,3,90,100)

select * from #table1

select
id,
SUM(zvalue1)*SUM(xvalue2) as sumvalue
from #table1
group by id
order by id

drop table #table1

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-17 : 02:04:27

SUM(zvalue1 * xvalue2) as sumvalue

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

maevr
Posting Yak Master

169 Posts

Posted - 2009-06-17 : 02:15:28

Thanks for the fast reply, ,but the sum is not correct, it should be
id=1: 5000 + 75000 = 80000
id=2: 500000
id=3: 1250 + 50 + 9000 = 10300

You cannot take the sum of all data belonging to zvalue1 for a unique id and multiply it with the sum of xvalue2 because it does not calculate according to my specification.

The query below calculates the sum per wallnr and id but after this I want to calculate the result based on id from the subquery:
SELECT id, wallnr, SUM(zvalue1 * xvalue2) AS mySum FROM #table1 GROUP BY id, wallnr

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-17 : 02:17:51

[code]
id sumvalue_Webfred sumvalue_Peso
1 120000 80000
2 500000 500000
3 80385 39800
[/code]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-17 : 02:29:21

maevr, why is no xvalue 123 incorporated in the productsum?
And what exactly is your specification? Do you think it's fair you share it with us?

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-17 : 02:37:12

Hi Peso,
I think it is an mistake in his last post for id=3.
Your solution works!
(30750 + 50 + 9000 = 39800)

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

maevr
Posting Yak Master

169 Posts

Posted - 2009-06-17 : 02:39:28

Hi Peso!
You have to calculate each wallnr for it self to get the correct value and then summarize them on id.

I know I can solve this using an view but I belive it is nicer to use a subquery instead.

The sum becomes incorrect if you take all zvalues from id1 and muliplies it with the sum of xvalue.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-17 : 02:41:18

Just a hunch...

-- Prepare sample data
DECLARE	@Sample TABLE
	(
		ID TINYINT,
		WallNr TINYINT,
		zValue INT,
		xValue INT
	)

INSERT	@Sample
	(
		ID,
		WallNr,
		zValue,
		xValue
	)
SELECT	1, 1,  50,  100 UNION ALL
SELECT	1, 2, 150,  500 UNION ALL
SELECT	2, 1, 500, 1000 UNION ALL
SELECT	3, 1, 250,  123 UNION ALL
SELECT	3, 2,   5,   10 UNION ALL
SELECT	3, 3,  90,  100

-- Peso
SELECT		ID,
		SUM(zValue * xValue) AS theSum
FROM		(
			SELECT		s.ID,
					s.WallNr,
					s.zValue,
					CASE
						WHEN s.xValue >= s.zValue THEN s.xValue
						ELSE (SELECT w.zValue FROM @Sample AS w WHERE w.ID = s.ID AND w.WallNr = s.WallNr + 1)
					END AS xValue
			FROM		@Sample AS s
		) AS d
GROUP BY	ID
ORDER BY	ID

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

maevr
Posting Yak Master

169 Posts

Posted - 2009-06-17 : 02:42:49

Thanks Peso!
My fault, I calculated wrong.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-17 : 02:45:40

quote:
Originally posted by maevr

Thanks Peso!
My fault, I calculated wrong.

So this is an acceptable solution?

-- Prepare sample data
DECLARE	@Sample TABLE
	(
		ID TINYINT,
		WallNr TINYINT,
		zValue INT,
		xValue INT
	)

INSERT	@Sample
	(
		ID,
		WallNr,
		zValue,
		xValue
	)
SELECT	1, 1,  50,  100 UNION ALL
SELECT	1, 2, 150,  500 UNION ALL
SELECT	2, 1, 500, 1000 UNION ALL
SELECT	3, 1, 250,  123 UNION ALL
SELECT	3, 2,   5,   10 UNION ALL
SELECT	3, 3,  90,  100

-- Peso
SELECT		ID,
		SUM(zValue * xValue) AS theSum
FROM		@Sample
GROUP BY	ID
ORDER BY	ID

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