Menu
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 
836
So now our function looks like this e(logx+logy) where log x + log y =log(x * y) which gives the product of all the values in the column which have been computed to the log values.
If you need to add a group of numbers in your table you can use the SUM function in SQL. This is the basic syntax: SELECT SUM(column_name) FROM table_name; The SELECT statement in SQL tells the computer to get data from the table.
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)]. ROUND is required because of the limited precision of the SQL variables.
The SQL multiply ( * ) operator is used to multiply two or more expressions or numbers.
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
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
179
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
