Tommy Sayugo - 4 months ago 22

SQL Question

Lets say I have table with 1 column like this:

`Col A`

1

2

3

4

If I

`SUM`

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

Source (Stackoverflow)

Comments