Is it possible doing cumulative multiply(below query) with window functions
select Id, Qty
into #temp
from(
select 1 Id, 5 Qty
union
select 2, 6
union
select 3, 3
)dvt
select
t1.Id
,exp(sum(log( t2.Qty))) CumulativeMultiply
from #temp t1
inner join #temp t2
on t2.Id <= t1.Id
group
by t1.Id
order
by t1.Id
Like:
select
t1.Id
,exp(sum(log( t2.Qty))) over (partition by t1.Id order by t1.Id rows between unbounded preceding and current row ) CumulativeMultiply
from #temp t1
inner join #temp t2
on t2.Id <= t1.Id
But get error:
The function 'exp' is not a valid windowing function, and cannot be used with the OVER clause
Update: Result that actually I want:
Id CumulativeMultiply
----------- ----------------------
1 5
2 30
3 90
no need of self join for Sum Over(Order by)
to find the previous records and multiply it
select
Id
,exp(sum(log( Qty))
over (order by Id )) CumulativeMultiply from #temp