Published on

Aggregate Product in SQL Server

Authors

Problem

Calculate 

PRODUCT
aggregate. Multiplication of values of a column in a result set. Similar to how 
SUM
, 
AVG
aggregate function works. There is no out-of-the-box function for the Product calculation.

Example

Calculate Product(Multiplication) of 

[Value]
grouping by 
[CategoryId]
for the following Table.

DECLARE @Temp_Table ASTABLE
(
                           [Value]float,
                           [CategoryId]int
)


INSERT INTO @Temp_Table (Id, Value, CategoryId)
VALUES (1, 1),
       (2, 1),
       (3, 1),
       (1, 2),
       (2, 2),
       (3, 3),
       (3, 3)

Expected Result Set

CategoryId
16(1*2*3)
22(1*2)
39(3*3)

Solution

SELECT CategoryId,
       ROUND(Exp(Sum(IIf([Value]=0,0,Log([Value]))))*IIf(Min([Value])=0,0,1),2)
FROM @Temp_Table
GROUP BY  CategoryId

Result

CategoryId
16
22
39

Happy Coding :)

Discuss on Twitter • View on GitHub