March 25, 2008 at 6:52 am
Hello guys! After surfing this forum and reading quite a few articles the last few days, I still haven't succeeded with a certain calculation that I need to carry out. Thus I wanted to ask if you could help me out 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
Whatever I attempt to do, it seems to fail and results in error-messages! 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
What do I need to do in order to get the output of that calculation for each row?
March 25, 2008 at 2:11 pm
Will something like this work?
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,
(
(
SELECT SUM(I_L.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
WHERE I_L.lfsnr LIKE '%1253'
) /ls.lmenge)
* (1/CONVERT(NUMERIC(9, 3), l.werksmassFM))
* (CONVERT(NUMERIC(9, 2), p.p_transpreis)) AS whatever_you_want_to_call_it
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 Redneck DBA
March 25, 2008 at 2:56 pm
It is often helpful to include the error messages you are getting. Or at least a summary of them. For example, if you are getting "divide by 0" errors, the solution is very different from "cannot convert varchar value to numeric value" type errors.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 25, 2008 at 8:16 pm
I would personally make a derived table with the sum and join it to the main query. If you use Jason's method, you will have to evaulate the subquery for each row and you only need the total once.
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,
--CALCULATION IS BELOW
((Sum_Lmenge.SumCol/ls.lmenge) * (1/werksmassFM)) * p_transpreis AS [MYNEWCOLUMN]
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 MyKey, SUM(I_L.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'
GROUP BY MyKey
) AS [Sum_Lmenge]
ON l.MyKey = Sum_Lmenge.MyKey
WHERE (l.lfsnr LIKE '%1253')
Additionally, a few other options included calculating the sum before you execute the query and use the variable with the sum.
e.g.
DECLARE @Sum NUMERIC(9,2)
SELECT @Sum = SUM(I_L.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
WHERE I_L.lfsnr LIKE '%1253'
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,
--CALCULATION IS BELOW
((@Sum/ls.lmenge) * (1/werksmassFM)) * p_transpreis AS [MYNEWCOLUMN]
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')
Another option is to use cross apply.
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,
--CALCULATION IS BELOW
((Sum_Lmenge.SumCol/ls.lmenge) * (1/werksmassFM)) * p_transpreis AS [MYNEWCOLUMN]
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_L.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')
Edited to make it fit on screen, without horizontal scroll.
March 25, 2008 at 8:43 pm
SQL Server 2005 has a new Over function that lets you apply an aggregate function over the entire table without the GROUP BY clause. That might be the simple solution here.
This example shows how to use it:
Use tempdb
go
create table test(col1 int, col2 varchar(20))
go
insert into test
select 1,'a'
union select 2,'b'
union select 3,'c'
union select 4,'d'
union select 5,'e'
select col1,col2,sum(col1) Over() as Total,
cast(col1 as decimal)/sum(col1) Over() * 100 as [Percent]
from test
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
March 25, 2008 at 8:55 pm
Interesting, I did not know OVER() could be used in this context. Thanks for pointing this out Kathi.
March 26, 2008 at 8:04 am
Hello guys,
sorry for letting you wait so long and for not replying! While I was working and following a different attempt to solve this calculation, you were quite busy as I can see. You guys are impressive! :w00t:
I can follow the statement but honestly wouldn't be able to think of something like that yet! So thanks for your efforts and support! I tried all of your methods but somewhere there is an error with "lmenge", which is an invalid column name according to the error message I receive. Once I figure out this error I might just have what I need! Thanks so far and don't hesitate to add something else that will help me. I am quite eager to learn from your expertise!
Thanks, Sebastian
March 26, 2008 at 9:27 am
You WILL recieve an error if you use the group by method I posted because I did not know what your keys were. Because I didnt know I put MyKey instead. This part should be altered to meet your table's needs.
March 26, 2008 at 10:16 am
@ Adam and all others
Good news: The code works! I just had to make some minor adjustements and now it looks like 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
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')
The question I have is, how can I format the numbers in the output column to have only 2 digits? I usually used CONVERT(NUMERIC(9, 2)... and experimented with format-number but without success!
So far I would like to thank you all for your splendid support and would like to say many thanks. I really appreciate it! 😀
March 26, 2008 at 10:21 am
Use converT, like you mentioned.
CONVERT(NUMERIC(9,2),werksmassFM / (Sum_Lmenge.SumCol/ls.lmenge)* p_transpreis)
March 26, 2008 at 10:23 am
The above will overflow if you have more than 7 digits to the left of your decimal. If this is the case you will have to go with more digits i.e. numeric(11,2).
March 27, 2008 at 2:37 am
Good Morning guys and thanks again for being so kind as to teach me how to deal with SQL! 🙂
Now I still have another question! After having calculated the following:
CONVERT(NUMERIC(9,2),werksmassFM / (Sum_Lmenge.SumCol/ls.lmenge)* p_transpreis) AS Rechnungsbetrag
I would like to calculate the sum of Rechnungsbetrag in a second statement and show all lines of the previous query as just one line. Most information like l.spediteur, p.punktName, p.holzart and p.laenge are identical for each row. Overall, I only need to calculate the sum of l.menge, Average(werksmassFM), and sum of Rechnungsbetrag based on the previous statement and show these values in one row.
I tried the following to start out, which doesn't work:
SUM(CONVERT(NUMERIC(9,2),werksmassFM / (Sum_Lmenge.SumCol/ls.lmenge)* p_transpreis)) AS Rechnungsbetrag
How can I build the sum of the sum that was calculated earlier? And is there a way to display several lines that were brought fourth by the previous statement in only one row?
March 27, 2008 at 7:33 am
SUM(CONVERT(NUMERIC(9,2),werksmassFM / (Sum_Lmenge.SumCol/ls.lmenge)* p_transpreis)) AS Rechnungsbetrag
This will only work if you use a group by in your query. You cannot perform aggregate functions without a group by, if multiple columns are selected. Your best bet is to use a derived table or a cross apply like we did before.
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
--SUM Rechnungsbetrag
CONVERT(NUMERIC(9,2),SUM_Rechnungsbetrag.SumR) AS [SUM_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]
CROSS APPLY(
SELECT SUM(werksmassFM / (t2.SumCol/ls.lmenge)* p_transpreis) AS [SumR]
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 [t2]
) AS [SUM_Rechnungsbetrag]
WHERE (l.lfsnr LIKE '%1253')AND (l.sped_journal = '11-08')
March 27, 2008 at 8:08 am
Adam Haines (3/27/2008)
SUM(CONVERT(NUMERIC(9,2),werksmassFM / (Sum_Lmenge.SumCol/ls.lmenge)* p_transpreis)) AS Rechnungsbetrag
This will only work if you use a group by in your query. You cannot perform aggregate functions
without a group by, if multiple columns are selected. Your best bet is to use a derived table or a
cross apply like we did before.
If you just want a grand total (not grouped), then you could just the SUM(value) OVER() notation (thanks for the tip Kathi K.!), no grouping needed. Otherwise, you can use the SUM(value) OVER (partition by group) notation.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 27, 2008 at 9:03 am
Matt, can you use over with the sum of a calculation? I have never tried, but I believe the over is used for deterministic columns. Like I said before I haven't tried.
To the OP the group by, cross apply, or derived table method will definitely work.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply