SQL Server Query - groupwise multiplication

Question

If we have a table like this:

Grp       Value

Grp1          2
Grp1          5
Grp1          3
Grp2          3
Grp2         -5
Grp2         -2
Grp3          4
Grp3          0
Grp3          1
Grp4         -2
Grp4         -4
Grp5          7
Grp5       NULL
Grp6       NULL
Grp6       NULL
Grp7         -1
Grp7        10

How can we group/multiply to get this?

GrpID     Value

Grp1       30
Grp2       30
Grp3        0
Grp4        8
Grp5        7
Grp6     NULL
Grp7      -10

gbn · Accepted Answer · 2011-08-30T15:57:11.310

Multiplying row values is the same as adding logarithms of row values

The trick is dealing with zeros and nulls.

Ok, checked now

DECLARE @foo TABLE (GrpID varchar(10), Value float)

INSERt @foo (GrpID, Value)
SELECT 'Grp1',          2
UNION ALL SELECT 'Grp1',          5
UNION ALL SELECT 'Grp1',          3
UNION ALL SELECT 'Grp2',          3
UNION ALL SELECT 'Grp2',         -5
UNION ALL SELECT 'Grp2',         -2
UNION ALL SELECT 'Grp3',          4
UNION ALL SELECT 'Grp3',          0
UNION ALL SELECT 'Grp3',          1
UNION ALL SELECT 'Grp4',         -2
UNION ALL SELECT 'Grp4',         -4
UNION ALL SELECT 'Grp5',          7
UNION ALL SELECT 'Grp5',       NULL
UNION ALL SELECT 'Grp6',       NULL
UNION ALL SELECT 'Grp6',       NULL
UNION ALL SELECT 'Grp7',         -1
UNION ALL SELECT 'Grp7',        10 

SELECT
    GrpID,
    CASE
       WHEN MinVal = 0 THEN 0
       WHEN Neg % 2 = 1 THEN -1 * EXP(ABSMult)
       ELSE EXP(ABSMult)
    END
FROM
    (
    SELECT
       GrpID, 
       --log of +ve row values
       SUM(LOG(ABS(NULLIF(Value, 0)))) AS ABSMult,
       --count of -ve values. Even = +ve result.
       SUM(SIGN(CASE WHEN Value < 0 THEN 1 ELSE 0 END)) AS Neg,
       --anything * zero = zero
       MIN(ABS(Value)) AS MinVal
    FROM
       @foo
    GROUP BY
       GrpID
    ) foo

@zvolkov: yes, it should. The sample data has no values >0 and <1 so missed this condition. Thanks — gbn, Aug 30 '11 at 15:57

Gimli · Answer 2 · 2016-05-04T20:43:00.173

This comes from CodePlex "Aggregate Product function extends SQL". I modified the statement to return the desired results except for the NULL group, it returns "1".

    SELECT GrpID, Exp(Sum(IIf(Abs(ISNULL(value,1))=0,0,Log(Abs(ISNULL(value,1))))))*IIf(Min(Abs(ISNULL(value,1)))=0,0,1)*
(1-2*(Sum(IIf(ISNULL(value,1)>=0,0,1)) % 2)) AS value 
    FROM @foo
    GROUP BY GrpID;

GrpID     Value

Grp1       30
Grp2       30
Grp3        0
Grp4        8
Grp5        7
Grp6        1
Grp7      -10

ref: http://productfunctionsql.codeplex.com/

score 1 · Answer 3 · answered Sep 13 '13 at 10:34

At does annoy me that there is no Multiply / Product aggregate function in SQL server. Unfortunately I didn't find the answer @gbn gave above until I'd already solved the problem a different way. I'm posting this alternative solution just in case it helps anyone, or if it turns out to be more efficient.

My solution basically involves using a recursive common table expression to multiply all the values together.

DECLARE @t TABLE (PID INT, ID INT, multiplier DECIMAL(14,5))

INSERT @t
        (PID, ID, multiplier)
VALUES  (1, 1, 1.5 )
      , (2, 1, 1.2)
      , (3, 2, 1.7)
      , (4, 3, 1)
      , (5, 4, 0.8)
      , (6, 4, 0.5)
      , (7, 4, 2)
      , (8, 4, 0.5)

SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY PID )
FROM @t;


WITH 
trn AS
(
SELECT PID, ID, multiplier, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY PID ) AS rn
FROM @t
),
tmu AS 
(
    SELECT DISTINCT 
        ID, 
        trn.multiplier AS multiplier,
        1 AS Iteration
    FROM trn WHERE rn = 1
    UNION ALL 
    SELECT 
        trn.ID, CAST(tmu.multiplier * trn.multiplier AS DECIMAL(14,5)) AS multiplier
        , tmu.Iteration + 1 AS Iteration
    FROM
        tmu --AS tmu1
        JOIN trn ON tmu.ID = trn.ID AND tmu.Iteration + 1 = trn.rn 
),
mxi AS
(
    SELECT ID, COUNT(ID) AS Iter
    FROM trn
    GROUP BY ID
)

SELECT tmu.* 
FROM tmu 
     JOIN mxi ON mxi.ID = tmu.ID AND mxi.Iter = tmu.Iteration
ORDER BY ID

Starting table (+ the partitioned row number) selects to:

PID ID  Multiplier  rn
1   1   1.50000 1
2   1   1.20000 2
3   2   1.70000 1
4   3   1.00000 1
5   4   0.80000 1
6   4   0.50000 2
7   4   2.00000 3
8   4   0.50000 4

List of ID / Multipliers all multiplied together selects to:

ID  multiplier  Iteration
1   1.80000 2
2   1.70000 1
3   1.00000 1
4   0.40000 4

SQL Server Query - groupwise multiplication

3 Answers3

Linked