Tommy Sayugo Tommy Sayugo - 4 months ago 22
SQL Question

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

Answer

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

Edit:

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
Comments