How to Multiply all values within a column with SQ

问题:

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

回答1:

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

---

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: http://sqlfiddle.com/#!3/01ddc/3/0

回答2:

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.

回答3:

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

SQLFiddle demo

回答4:

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

SQL FIDDLE

回答5:

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.