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
Matt
  • 14,906
  • 27
  • 99
  • 149
Tommy Sayugo
  • 375
  • 2
  • 5
  • 16

5 Answers5

41

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

Community
  • 1
  • 1
Matt
  • 14,906
  • 27
  • 99
  • 149
  • @Alex Sure, what don't you feel is clearly explained? – Matt Jun 05 '15 at 12:41
  • what is exp? and explanation of the equation – user786 Jun 05 '15 at 12:42
  • 2
    My Math skills obviously need some improvement. Nice answer! – xQbert Jun 05 '15 at 13:06
  • 1
    Brilliant answer. Higher mathematics rules! – SQL Police Nov 19 '15 at 12:53
  • 1
    @SQLPolice Thanks! I am quite proud of this one ! – Matt Nov 19 '15 at 12:54
  • what if the cols contain a value as zero – Kishan Kumar Jul 15 '16 at 08:49
  • @KishanKumar updated answer to deal with "An invalid floating point operation occurred." (When you have a 0 in your data). – Matt Jul 15 '16 at 08:58
  • @Matt now its ok. But i would like to get an answer of 0 when the [col A] = 0 – Kishan Kumar Jul 15 '16 at 09:04
  • @Matt I am sorry but when you have 0 the result should be 0. Not null or anything else but 0. Most of time if you have a 0 in your set of data for this kind of operation it is most of time a data input error (I agree). But that's another story. – Marco Guignard Jul 15 '16 at 09:43
  • @MarcoGuignard with regards to only having 0 i would leave it open to how the end user would want to resolve this depending on their other data, something as basic as wrapping it all in a `CASE` and saying when result is `NULL` then `0` OR putting a `CASE` inside the calculation (with the possibility of needing to `GROUP` data. – Matt Jul 15 '16 at 09:48
  • Nice answer, but did you consider negative numbers where LOG doesn't work? in that case you should use ABS function to calculate the magnitude and then compute the sign of product – Alireza Feb 22 '17 at 11:11
  • @Alireza I did not, but i have now, added an extra answer (and sql fiddle) to deal with negative numbers. – Matt Feb 22 '17 at 15:48
  • For the countless people trying to apply this expression to SQLite with endless grief, you need to change `LOG` to `LN` to make it work. – swdev Mar 11 '21 at 07:10
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

juergen d
  • 201,996
  • 37
  • 293
  • 362
3

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • @Matt . . . First, that is dependent on the database, but not surprising. If the OP knows the values are integers, then s/he can round the value. If an integer is *not* expected, then 23.999999999999993 is close enough to 24 for any purpose I can think of. – Gordon Linoff Jun 05 '15 at 12:30
  • wasn't questioning your logic and 23.999999.... is close enough for most purposes. – Matt Jun 05 '15 at 12:34
  • @Matt . . . Your assumption that the values are integers is reasonable, based on the question, but it is not explicitly stated in the question. – Gordon Linoff Jun 05 '15 at 12:35
  • absolutely, however like most questions from new users, we will most likely never find out! – Matt Jun 05 '15 at 12:37
2

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

Stanislovas Kalašnikovas
  • 9,203
  • 4
  • 32
  • 59
  • Very elegant. But instead of mucking about with `COALESCE` and confusing the whole issue: why don't you simply initialise `@var = 1` before the `SELECT` statement? – Disillusioned Aug 15 '16 at 09:48
2

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.

Sparrow
  • 2,548
  • 1
  • 24
  • 28
Discipulus
  • 245
  • 1
  • 3
  • 13