Search code examples
sqlsql-servert-sqlwindow-functionsexp

Cumulative multiplication with window functions, 'exp' is not a valid windowing function


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

Solution

  • 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