CODEWITHSUNDEEP
sql
Sethu
Sethu

Reputation: 82

SQL Query for cumulative Multiplication and Sum

I am trying to achieve the below manipulated cumulative sum

enter image description here

Have written the following SQL , however this bit of the query needs correction ELSE ( Exp(Sum(Log(Abs(NULLIF( total , 0))))) +value ) as the +value bit is not being taken in to account for consecutive aggregations.

Thanks for your help!!

with DTA AS
(
select *,  case when id = 1  then  value*Multiplier  else  Abs(NULLIF(Multiplier,0))  end as total from testTable
)
,
DTB AS (
SELECT id,value,Multiplier,total,
(SELECT 
       CASE              
                 WHEN B.id = 1 THEN CONVERT(decimal(18,5), B.total)                                              
                 ELSE ( Exp(Sum(Log(Abs(NULLIF( total  , 0))))) +value ) 
               END
        FROM   DTA a 
        WHERE  B.id >= A.id) as  cumulativeSum

FROM   DTA B
)

select * from DTB order by id asc

Upvotes: 1

Views: 673

Answers (2)

Sethu
Sethu

Reputation: 82

Have resolved it by adding a custom aggregate function via C# and registering the dll in SQL server.

Reference :Custom Aggregates in SQL Server

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270431

You can just do:

select tt.*,
       exp(sum(log(nullif(abs(value * multiplier), 0))) over (order by id)) as cumulativeProduct
from testTable tt;

Upvotes: 1

Related Questions