March 27, 2008 at 9:15 am
Adam Haines (3/27/2008)
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.
I'm not sure if I fully understand the question. If you're looking at:
sum(value) OVER (partition by GroupID)
GroupID must be a column (or a column list) pulled out of the FROM tables, and cannot be a direct calculation. Of course - that just means you could "hide" any calculations in a sub-query.
However, I am not aware of any limitations on what would end up in the "value" spot. The VALUE could be any bizarre calculation you can think of, deterministic or not. Non-deterministic might be ugly, since your query would return a different result each time, but it would run.
does that answer it?
----------------------------------------------------------------------------------
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:33 am
does that answer it?
Not quite.
Basically the question is can you put this into over().
sum(werksmassFM / (Sum_Lmenge.SumCol/ls.lmenge)* p_transpreis)
March 27, 2008 at 10:03 am
This runs:
drop table #mytemptest
go
create table #mytemptest (id int identity(1,1) primary key clustered,
groupid int,
amt1 numeric(18,4),
amt2 numeric(18,4),
amt3 numeric(18,4),
amt4 numeric(18,4))
go
insert #mytemptest (groupid, amt1, amt2,amt3,amt4)
select top 10000 rand(checksum(newid()))*40,
rand(checksum(newid()))*400,
rand(checksum(newid()))*400,
rand(checksum(newid()))*400,
rand(checksum(newid()))
from sys.all_columns sc1, sys.all_columns sc2
go
select *,
sum(amt1/(amt2/amt3)*amt4) OVER() as tot,
sum(amt1/(amt2/amt3)*amt4) OVER(PARTITION by GROUPID) as Grouptot,
(sum(amt1/(amt2/amt3)*amt4) OVER(PARTITION by GROUPID))
/(sum(amt1/(amt2/amt3)*amt4) OVER()) as GroupPerctot
from #mytemptest
The only new requirement I can see seem to be on what goes in the OVER() clause. If you could put it into a SUM() before, looks to me that you can put it into a SUM() OVER().
----------------------------------------------------------------------------------
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 10:32 am
Thanks for doing the legwork :). I have been learning a lot of new stuff, in the past few days 😀
March 27, 2008 at 10:51 am
Adam Haines (3/27/2008)
Thanks for doing the legwork :). I have been learning a lot of new stuff, in the past few days 😀
Notice I just said "it runs", and not "it runs...well". Not so sure it's all that great on performance. May be time to do some "compare and contrast" testing: this smells a bit of too much going on in one single query. Will need to check over the exec plan "in my spare time"...
----------------------------------------------------------------------------------
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?
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply