Calculation with SUM (Help needed)

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

Calculation with SUM (Help needed)

Author

Topic

skahlert
Starting Member

6 Posts

Posted - 2008-03-25 : 08:39:07

Hello experts! After surfing this forum and reading many articles in the web I still haven't succeeded with a certain calculation that I need to perform. Thus I wanted to address you to ask if you could help me on this one:

I have 3 tables in a SQL Server 2005 DB and want to carry out the following calaculation:

[sum of lmenge/lmenge]* [1/werksmassFM]*p_transpreis

However, whatever I attempt to do, it seems to fail! I am quite a newbe and was never focussed with such comprehensive statements before. I would appreciate any help!

My statement looks like this so far (without the calculation):

SELECT l.bestellnr, l.lfsnr, l.ziel, l.lfs_refnr, l.lfsGuid, CONVERT(NUMERIC(9, 3), l.werksmassFM) AS werksmassFM, ls.lmenge, ls.pguid_ref, p.punktName, p.holzart, CONVERT(NUMERIC(9, 2), p.laenge) AS laenge, CONVERT(NUMERIC(9, 2), p.p_transpreis) AS p_transpreis
FROM dbo.lfs AS l INNER JOIN dbo.lfs_sub AS ls ON l.lfsGuid = ls.lfsGuid_ref INNER JOIN dbo.polter AS p ON ls.pguid_ref = p.pguid
WHERE (l.lfsnr LIKE '%1253')

The respective output data looks like this:

bestellnr >>> SJT07004A
lfsnr >>> HNAU01253
ziel >>> SE Ybbs
lfs_refnr >>> 1350-8
lfsGuid >>> de8ea3bdbdece
werksmassFM >>> 33.450
lmenge >>> 23,07692
pguid_ref >>> 53e7f6224435f244a6ba800bb33448b4
punktName >>> 470
holzart >>> Fi
laenge >>> 4.20
p_transpreis >>> 8.50

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-25 : 08:45:48

Do it without integer math.
Use

[sum of lmenge] / lmenge * 1.0 / werksmassFM * p_transpreis

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

skahlert
Starting Member

6 Posts

Posted - 2008-03-25 : 10:24:09

Thanks for your answer Peso! However, I guess your hint is not the true cause for my problem. Now as I have inserted the calculation into my statement I receive the error message that there cannot be an aggregate in the "On clause" unless it is a part of a subquery, which is part of a having clause.

Maybe you can check what exactly is wrong with my code!?

SELECT l.bestellnr, l.lfsnr, l.ziel, l.lfs_refnr, l.lfsGuid, l.lfs_transportnr, CONVERT(NUMERIC(9, 3), l.werksmassFM) AS werksmassFM, ls.lmenge, ls.pguid_ref, p.punktName, p.holzart, CONVERT(NUMERIC(9, 2), p.laenge) AS laenge, CONVERT(NUMERIC(9, 2), p.p_transpreis) AS p_transpreis
FROM dbo.lfs AS l INNER JOIN dbo.lfs_sub AS ls ON l.lfsGuid = ls.lfsGuid_ref INNER JOIN dbo.polter AS p ON ls.pguid_ref = p.pguid AND ls.lmenge = ((SUM(ls.lmenge) / ls.lmenge) * ('1.0/werksmassFM')) * p.p_transpreis
WHERE (l.lfsnr LIKE '%1253')

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-25 : 10:29:10

Why the use of '-characters? Drop them, because '1.0/weksmassFM' is not a field/column is it?

AND ls.lmenge = SUM(ls.lmenge) / ls.lmenge * 1.0 / werksmassFM * p.p_transpreis

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

skahlert
Starting Member

6 Posts

Posted - 2008-03-25 : 11:00:13

Hello Peso! WerksmassFM is a field, which I need. It stores the amount of timber in m³!
I used to have the same statement as you just suggested but thought it should be different. In fact I want to divide the sum of lmenge with lmenge for each dataset. Subsequently I need to divide the field "werksmassFM" with the result of sum(lmenge)/lmenge. Finally the result of that calculation should be multiplied by p_transpreis! Does this give you a better idea of what I want to do? Maybe all the brackets were quite confusing and unneccessary. Still, no calculation is carried out as I receive the message "1015", which I tried to explain above. (Unfortunately I am using another language setting as you and cannot copy the message directly!).

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-25 : 11:07:37

[code]SELECT l.bestellnr,
l.lfsnr,
l.ziel,
l.lfs_refnr,
l.lfsGuid,
l.lfs_transportnr,
CONVERT(NUMERIC(9, 3), l.werksmassFM) AS werksmassFM,
ls.lmenge,
ls.pguid_ref,
p.punktName,
p.holzart,
CONVERT(NUMERIC(9, 2), p.laenge) AS laenge,
CONVERT(NUMERIC(9, 2), p.p_transpreis) AS p_transpreis
FROM dbo.lfs AS l
INNER JOIN dbo.lfs_sub AS ls ON l.lfsGuid = ls.lfsGuid_ref
INNER JOIN dbo.polter AS p ON ls.pguid_ref = p.pguid
WHERE l.lfsnr LIKE '%1253'
-- AND ls.lmenge = SUM(ls.lmenge) / ls.lmenge * 1.0 / werksmassFM * p.p_transpreis[/code]
The last line is the offending line.
You are doing what here, exactly?

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-25 : 11:09:05

Try this

SELECT		l.bestellnr,
		l.lfsnr,
		l.ziel,
		l.lfs_refnr,
		l.lfsGuid,
		l.lfs_transportnr,
		CONVERT(NUMERIC(9, 3), l.werksmassFM) AS werksmassFM,
		ls.lmenge,
		ls.pguid_ref,
		p.punktName,
		p.holzart,
		CONVERT(NUMERIC(9, 2), p.laenge) AS laenge,
		CONVERT(NUMERIC(9, 2), p.p_transpreis) AS p_transpreis
FROM		dbo.lfs AS l
INNER JOIN	dbo.lfs_sub AS ls ON l.lfsGuid = ls.lfsGuid_ref
INNER JOIN	dbo.polter AS p ON ls.pguid_ref = p.pguid
INNER JOIN	(
			SELECT		lfsGuid_ref,
					SUM(lmenge) AS lmenge
			FROM		dbo.lfs_sub
			GROUP BY	lfsGuid_ref
		) AS d ON d.lfsGuid_ref = l.lfsGuid
WHERE		l.lfsnr LIKE '%1253'
		AND ls.lmenge = d.lmenge / ls.lmenge * 1.0 / l.werksmassFM * p.p_transpreis

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

skahlert
Starting Member

6 Posts

Posted - 2008-03-26 : 04:45:58

Good Morning Peso! Thanks for taking the time to help me!
I tried your proposal and played a little around with it. As a consequence I don't get any error message anymore (many thanks to you) but no values are forwarded either. I assume it's because the result of the calculation is not given out in the same row with the other data! If I remove "AND ls.lmenge = d.lmenge / ls.lmenge * 1.0 / l.werksmassFM * p.p_transpreis" from the statement I will filter exactly 3 datasets. If I remove the filter option I consequently receive an error message since one or more values in all datasets include a NULL value, which is not valid as you know. Hence I guess your statement works but data is simply not displayed do to the output display, which needs to be declared.
Well, I am sorry for my incapability to tell you about these issues in expert terminology, but I am trying to do my best!

The last line is the offending line: You're right!
Here I want to calculate the amount of volume for each pile of timber that my selection filters. I will tell you more about it:
"lmenge" is the estimated amount of timber of each pile, which has been delivered to the mill according to the truck driver. "WerksmassFM" in turn is the real sum of volume as calculated by the sawmill's high-tech sensors. It is in fact the amount, which is taken to calculate how much money the driver gets for the transport. Unfortunately I have only the sum (werksmassFM) for all piles that were loaded on the truck and need to break the sum down again to have a share for each pile.
Since each pile holds a different transportprice (p_transpreis) I need to calculate the share of "werkmsmassFM for each pile" with the corresponding transport price. This is the ultimate value, which I am striving for!
The calculation is as follows:
1. Step: divide the "sum of lmenge" by "lmenge"
2. Step: Divide "WerksmassFM" by the result of Step 1
3. Step: Multiply "p_transpreis" by result of Step 2

Well Peso, I hope you can give me some more aid. Thanks a lot in advance!!!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-26 : 05:49:53

These three rules
1. Step: divide the "sum of lmenge" by "lmenge"
2. Step: Divide "WerksmassFM" by the result of Step 1
3. Step: Multiply "p_transpreis" by result of Step 2

yields

AND ls.lmenge = 1.0 * l.werksmassFM * ls.lmenge / d.lmenge * p.p_transpreis

which is the same as

AND d.lmenge = l.werksmassFM * p.p_transpreis

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

skahlert
Starting Member

6 Posts

Posted - 2008-03-26 : 08:17:36

Hey Peso, I wasn't sure how precisely I have to write the calculation, which is the reason why I took recourse to the longer statement.

Still I think the calculation should be like: AND ls.lmenge = 1.0 / l.werksmassFM * ls.lmenge / d.lmenge * p.p_transpreis
However, slight adjustments to the calculation can still be made when I see the result of the query.

Now I only have to figure out a way to display the result of the calculation in a new column next to the other values I have queried.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-26 : 09:11:21

1a) AND ls.lmenge = 1.0 / l.werksmassFM * ls.lmenge / d.lmenge * p.p_transpreis
1b) AND ~~ls.lmenge~~ = 1.0 / l.werksmassFM * ~~ls.lmenge~~ / d.lmenge * p.p_transpreis

2a) AND 1.0 = 1.0 / l.werksmassFM / d.lmenge * p.p_transpreis
2b) AND l.werksmassFM * d.lmenge = p.p_transpreis

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

skahlert
Starting Member

6 Posts

Posted - 2008-03-27 : 05:11:26

Hello Peso! Sorry for not answering yesterday!

FYI!
What I needed is this:

DECLARE @Sum NUMERIC(9,2)
SELECT @Sum = SUM(I_LS.lmenge)
FROM dbo.lfs AS I_L
INNER JOIN dbo.lfs_sub AS I_LS
ON I_L.lfsGUID = I_LS.lfsguid_ref
INNER JOIN dbo.polter AS I_P
ON I_LS.pguid_ref = I_P.pguid
--Nicht unbedingt notwendig >>> beschleunigt die Abfrage
WHERE I_L.lfsnr LIKE '%1253'

SELECT
l.spediteur AS [Spediteur],
l.lfsnr AS [Lieferschein-Nr.],
l.lfs_refnr [Lieferreferenz],
l.lfs_transportnr As [Transport-Nr.],
CONVERT(VARCHAR(10), l.datum, 104) AS [Lfs.Datum],
l.bestellnr AS [Stockkauf-Nr.],
l.ziel AS [Empfänger],
l.waggonnr As [Waggonnr.],
l.kundenauftragsnr As [Verkaufsnr.],
l.kennzeichen As [Kennzeichen],
CONVERT(NUMERIC(9, 2), l.werksmassFM) AS [WM in FM],
ls.lmenge As [Lfs.-Menge],
p.punktName AS Polter,
p.holzart AS Holzart,
CONVERT(NUMERIC(9, 2), p.laenge) AS Länge,
CONVERT(NUMERIC(9, 2), p.p_transpreis) AS Transportpreis,

--Die Rechnung
CONVERT(NUMERIC(9,2),werksmassFM / (Sum_Lmenge.SumCol/ls.lmenge)* p_transpreis) AS Rechnungsbetrag
FROM dbo.lfs AS l
INNER JOIN dbo.lfs_sub AS ls
ON l.lfsGuid = ls.lfsGuid_ref
INNER JOIN dbo.polter AS p
ON ls.pguid_ref = p.pguid
CROSS APPLY(
SELECT SUM(I_LS.lmenge) AS [SumCol]
FROM dbo.lfs AS I_L
INNER JOIN dbo.lfs_sub AS I_LS
ON I_L.lfsGUID = I_LS.lfsguid_ref
INNER JOIN dbo.polter AS I_P
ON I_LS.pguid_ref = I_P.pguid
WHERE I_L.lfsnr LIKE '%1253'
) AS [Sum_Lmenge]
WHERE (l.lfsnr LIKE '%1253')AND (l.sped_journal = '11-08')
--------------------------------------------------------------

I just wanted to show you so you know what I was talking about! I appreciate your efforts very much and am very thankful that you took the time to help me. Thanks, Sebastian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources