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
``````

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
``````