如何使用像SUM（）这样的SQL将列中的所有值相乘

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以便得到以下内容？

Col A
24

5 个解决方案

#1

Using a combination of ROUND, EXP, SUM and LOG

使用ROUND，EXP，SUM和LOG的组合

SELECT ROUND(EXP(SUM(LOG([Col A]))),1)
FROM yourtable

SQL Fiddle: http://sqlfiddle.com/#!3/d43c8/2/0

SQL小提琴：http：//sqlfiddle.com/#！3 / d43c8 / 2/0

Explanation

说明

LOG returns the logarithm of col a ex. LOG([Col A]) which returns

LOG返回col的对数。 LOG（[Col A]）返回

0
0.6931471805599453
1.0986122886681098
1.3862943611198906

Then you use SUM to Add them all together SUM(LOG([Col A])) which returns

然后使用SUM将它们全部加在一起SUM（LOG（[Col A]））返回

3.1780538303479453

Then the exponential of that result is calculated using EXP(SUM(LOG(['3.1780538303479453']))) which returns

然后使用EXP（SUM（LOG（['3.1780538303479453']）））计算该结果的指数，返回

23.999999999999993

Then this is finally rounded using ROUND ROUND(EXP(SUM(LOG('23.999999999999993'))),1) to get 24

然后使用ROUND ROUND（EXP（SUM（LOG（'23 .999999999999993'））），1）最终舍入，得到24

---

Extra Answers

Simple resolution to:

简单的解决方案：

An invalid floating point operation occurred.

发生了无效的浮点运算。

When you have a 0 in your data

当数据中有0时

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.

如果你只有0那么上面会得到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

SQL小提琴：http：//sqlfiddle.com/#！03/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.

注意：您没有指定数据库。在某些数据库中，您将使用LN（）而不是LOG（）。模数运算符（处理负值）的函数也因数据库而异。

#3

In MySQL you could use

在MySQL中你可以使用

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.

你可以通过在后面声明一个变量来做到这一点，COALESCE用于避免NULLS。

DECLARE @var INT

SELECT @var = Col1 * COALESCE(@var, 1) FROM Tbl

SELECT @var

SQL FIDDLE

SQL FIDDLE

#5

A quick example, supposing that the column contains only two values: a and b, both different than zero.

一个简单的例子，假设该列只包含两个值：a和b，两者都不为零。

We are interested in x = a*b. Then, applying some math, we have:

我们对x = a * b感兴趣。然后，应用一些数学，我们有：

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)

SELECT ROUND（EXP（SUM（LOG（[Col A]））），1）

FROM your table

从你的桌子

ROUND is required because of the limited precision of the SQL variables.

由于SQL变量的精度有限，因此需要ROUND。