How to Multiply all values within a column with SQL like SUM()

Lets say I have table with 1 column like this:

Col A 1 2 3 4 

If I SUM it, then I will get this:

Col A 10 

My question is: how do I multiply Col A so I get the following?

Col A 24 

5 Answers

Using a combination of ROUND, EXP, SUM and LOG

SELECT ROUND(EXP(SUM(LOG([Col A]))),1) FROM yourtable 

SQL Fiddle:

Explanation

LOG returns the logarithm of col a ex. LOG([Col A]) which returns

0 0.6931471805599453 1.0986122886681098 1.3862943611198906 

Then you use SUM to Add them all together SUM(LOG([Col A])) which returns

3.1780538303479453 

Then the exponential of that result is calculated using EXP(SUM(LOG(['3.1780538303479453']))) which returns

23.999999999999993 

Then this is finally rounded using ROUND ROUND(EXP(SUM(LOG('23.999999999999993'))),1) to get 24

Extra Answers

Simple resolution to:

An invalid floating point operation occurred.

When you have a 0 in your data

SELECT ROUND(EXP(SUM(LOG([Col A]))),1) FROM yourtable WHERE [Col A] != 0 

If you only have 0 Then the above would give a result of NULL.

When you have negative numbers in your data set.

SELECT (ROUND(exp(SUM(log(CASE WHEN[Col A]<0 THEN [Col A]*-1 ELSE [Col A] END))),1)) * (CASE (SUM(CASE WHEN [Col A] < 0 THEN 1 ELSE 0 END) %2) WHEN 1 THEN -1 WHEN 0 THEN 1 END) AS [Col A Multi] FROM yourtable 

Example Input:

1 2 3 -4 

Output:

Col A Multi -24 

SQL Fiddle:

In MySQL you could use

select max(sum) from ( select @sum := @sum * colA as sum from your_table cross join (select @sum := 1) s ) tmp 

SQLFiddle demo

This is a complicated matter. If you want to take signs and handle zero, the expression is a bit complicated:

select (case when sum(case when a = 0 then 1 else 0 end) > 0 then 0 else exp(sum(log(abs(a)))) * (case when sum(case when a < 0 then 1 else 0 end) % 2 = 1 then -1 else 1 end) end) as ProductA from table t; 

Note: you do not specify a database. In some databases you would use LN() rather than LOG(). Also the function for the modulo operator (to handle negative values) also differs by database.

You can do It simply by declaring an variable in following, COALESCE is used to avoid NULLS.

DECLARE @var INT SELECT @var = Col1 * COALESCE(@var, 1) FROM Tbl SELECT @var 

SQL FIDDLE

A quick example, supposing that the column contains only two values: a and b, both different than zero.

We are interested in x = a*b. Then, applying some math, we have:

x = a * b -> log(x) = log(a * b) -> log(x) = log(a) + log(b) -> exp[log(x)] = exp[log(a) + log(b)] -> x = exp[log(a) + log(b)]. 

Therefore:

a * b = exp[log(a) + log(b)] 

This explains Matt's answer:

SELECT ROUND(EXP(SUM(LOG([Col A]))),1)

FROM your table

ROUND is required because of the limited precision of the SQL variables.