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

Question

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 

Answer 1

Using a combination of ROUND, EXP, SUM and LOG

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

SQL Fiddle: http://sqlfiddle.com/#!3/d43c8/2/0

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: http://sqlfiddle.com/#!3/01ddc/3/0

Answer 2

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