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
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
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
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
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.
Using a combination of
ROUND
,EXP
,SUM
andLOG
SQL Fiddle: http://sqlfiddle.com/#!3/d43c8/2/0
Explanation
LOG
returns the logarithm of col a ex.LOG([Col A])
which returnsThen you use
SUM
to Add them all togetherSUM(LOG([Col A]))
which returnsThen the exponential of that result is calculated using
EXP(SUM(LOG(['3.1780538303479453'])))
which returnsThen this is finally rounded using
ROUND
ROUND(EXP(SUM(LOG('23.999999999999993'))),1)
to get24
Extra Answers
Simple resolution to:
When you have a
0
in your dataIf you only have
0
Then the above would give a result ofNULL
.Example Input:
Output:
SQL Fiddle: http://sqlfiddle.com/#!3/01ddc/3/0