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...
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
5 Answers
Using a combination of ROUND, EXP, SUM and LOG
SELECT ROUND(EXP(SUM(LOG([Col A]))),1)
FROM yourtable
SQL Fiddle:
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
Must Read
Extra Answers
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:
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
Marcus Vance is a cybersecurity auditor and technology writer dedicated to educating the public about online safety, data privacy regulations, enterprise security, and emerging cyber threats.
