3

Not sure is this the right title. I need to find the cumulative multiplication as like running total.

Searched the forum and got a excellent answer. But it is not the exact answer for me.

so modified the answer to my requirement.

SELECT *,
       (SELECT CASE
                 WHEN Min(Abs(Column1)) = 0 THEN 0
                 ELSE Exp(Sum(Log(Abs(NULLIF(Column1, 0))))) -- the base mathematics
                      * Round(0.5 - Count(NULLIF(Sign(Sign(Column1) + 0.5), 1))%2, 0) -- pairs up negatives
               END
        FROM   TEMP a
        WHERE  B.ID >= A.ID) as  Running_Mul
FROM   TEMP B

And I got my answer. Now Is there any better way of doing this in Sql Server 2008?

Sample data:

ID  Column1 
--  -------
1   1       
2   2       
3   4       
4   8       
5   -2

Expected Result: 

ID  Column1 Running_Mul
--  ------- -----------
1   1       1
2   2       2
3   4       8
4   8       64
5   -2      -128

Sql Fiddle

Community
  • 1
  • 1
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172

2 Answers2

1

Your method is pretty reasonable. Good catch on the nullif() in the sum(), by the way. Although the else clause is computed only after the then, components of the else are calculated during the aggregation -- so log(0) would return an error.

I think there are some simpler ways to calculate the sign, such as:

power(-1, sum(case when column1 < 0 then 1 else 0 end))

or:

(case when sum(case when column1 < 0 then 1 else 0 end) % 2 = 0 then 1 else -1 end)

However, which version is "simpler" is a matter of opinion.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Can share a example when my query will fail. I didn't get what you are trying to say sorry. – Pரதீப் Feb 09 '15 at 13:12
  • @NoDisplayName . . . Your version won't fail. Sorry I didn't make that point clear. It is a subtly that `nullif()` is needed in the `case` statement, even though the documentation says that the clauses are evaluated in order. – Gordon Linoff Feb 09 '15 at 21:45
1

Here is another approach which I use in my SPs :

USE DB
GO

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO

IF(OBJECT_ID('TEMP') IS NOT NULL)
DROP TABLE TEMP

CREATE TABLE TEMP (ID INT, Column1 INT)
INSERT INTO TEMP VALUES
(1,1), 
(2,2), 
(3,4), 
(4,8), 
(5,-2)  

DECLARE @result TABLE(ID INT, Column1 INT, calc INT)
DECLARE @Calc INT = 1

INSERT INTO @result (ID,Column1)
SELECT ID,Column1 FROM TEMP ORDER BY ID

UPDATE @result SET @Calc = calc = Column1 * @Calc

SELECT * FROM @result

I found a blog in which different methods to solve such problem, have been compared. check here.

Deep
  • 3,162
  • 1
  • 12
  • 21
  • In that link it is mentioned that **Note: There is a pretty big assumption in using the “Update to local variable” method. This is that the Update statement will update the rows in the temp table in the correct order. There is no simple way to specify the order for an Update statement, so potentially this method could fail, although I have not seen this actually happen yet!** – Pரதீப் Feb 11 '15 at 09:20
  • Yup! that's true. If somehow update doesn't go in the correct order, it will not give desired results but I haven't seen such situation yet. Here is the similar question and answer which states the same : http://stackoverflow.com/questions/11310877/calculate-running-total-running-balance – Deep Feb 12 '15 at 05:35