Write a query to get the product of all the values of a column in SQL Server

Write a query to get the product of all the values of a column in SQL Server

Idea is little bit tricky but very easy to understand. Since there is no any multiplication function like other aggregate functions in SQL Server, we need create a function which will behave like multiplication function using existing functions. Before writing the query I would strongly recommend you to understand how we use Log( ) function and exponential function

In Mathematics,

log x + log y = log(x * y)

and exponential function is exactly reverse of log. i.e. e^logx = x  so e^log(x * ^y) = x * y

Suppose we have a table containing two rows as shown below and we want product of all the rows in a particular column.

Salary

100

50

100*50 = 5000 (Result)

here, we are taking log of each record and then doing the summation

log (100) + log (50)= log (100 * 50)

 Now, we will take exponential of above result.

e^{log(100 * 50)}  = 100*50

Summarized: Sum the log values and take the exponential to get the product of values, EXP(SUM(log(Values))).

SELECT EXP(SUM(log(salary))) FROM EMPLOYEE

Second method:

DECLARE @a VARCHAR(100)

SELECT @a = COALESCE(@a+' * ' , '') + CAST(Salary as varchar(100))  from #Test

--PRINT @a

EXEC ('SELECT ' + @a)