Multiplication Aggregate Operator in Sql

Multiplication aggregate operator in SQL

By MUL do you mean progressive multiplication of values?

Even with 100 rows of some small size (say 10s), your MUL(column) is going to overflow any data type! With such a high probability of mis/ab-use, and very limited scope for use, it does not need to be a SQL Standard. As others have shown there are mathematical ways of working it out, just as there are many many ways to do tricky calculations in SQL just using standard (and common-use) methods.

Sample data:

Column
1
2
4
8

COUNT : 4 items (1 for each non-null)
SUM   : 1 + 2 + 4 + 8 = 15
AVG   : 3.75 (SUM/COUNT)
MUL   : 1 x 2 x 4 x 8 ? ( =64 )

For completeness, the Oracle, MSSQL, MySQL core implementations *

Oracle : EXP(SUM(LN(column)))   or  POWER(N,SUM(LOG(column, N)))
MSSQL  : EXP(SUM(LOG(column)))  or  POWER(N,SUM(LOG(column)/LOG(N)))
MySQL  : EXP(SUM(LOG(column)))  or  POW(N,SUM(LOG(N,column)))

Care when using EXP/LOG in SQL Server, watch the return type http://msdn.microsoft.com/en-us/library/ms187592.aspx
The POWER form allows for larger numbers (using bases larger than Euler's number), and in cases where the result grows too large to turn it back using POWER, you can return just the logarithmic value and calculate the actual number outside of the SQL query

* LOG(0) and LOG(-ve) are undefined. The below shows only how to handle this in SQL Server. Equivalents can be found for the other SQL flavours, using the same concept

create table MUL(data int)
insert MUL select 1 yourColumn union all
           select 2 union all
           select 4 union all
           select 8 union all
           select -2 union all
           select 0

select CASE WHEN MIN(abs(data)) = 0 then 0 ELSE
       EXP(SUM(Log(abs(nullif(data,0))))) -- the base mathematics
     * round(0.5-count(nullif(sign(sign(data)+0.5),1))%2,0) -- pairs up negatives
       END
from MUL

Ingredients:

taking the abs() of data, if the min is 0, multiplying by whatever else is futile, the result is 0
When data is 0, NULLIF converts it to null. The abs(), log() both return null, causing it to be precluded from sum()
If data is not 0, abs allows us to multiple a negative number using the LOG method - we will keep track of the negativity elsewhere
Working out the final sign
- sign(data) returns 1 for >0, 0 for 0 and -1 for <0.
- We add another 0.5 and take the sign() again, so we have now classified 0 and 1 both as 1, and only -1 as -1.
- again use NULLIF to remove from COUNT() the 1's, since we only need to count up the negatives.
- % 2 against the count() of negative numbers returns either
- --> 1 if there is an odd number of negative numbers
- --> 0 if there is an even number of negative numbers
- more mathematical tricks: we take 1 or 0 off 0.5, so that the above becomes
- --> (0.5-1=-0.5=>round to -1) if there is an odd number of negative numbers
- --> (0.5-0= 0.5=>round to 1) if there is an even number of negative numbers
- we multiple this final 1/-1 against the SUM-PRODUCT value for the real result

Aggregate function to multiply all values

Yes, there's a technique described here in this blog:

Basically you take the natural log of SUM and then do an exponential (e^x)

SELECT EXP (SUM (LN (col))) as product from t;

Since the output of this would be a floating point, you may do a FLOOR

FLOOR( EXP (SUM (LN (col))) )

DEMO

Note: I've just found that this would fail if one of the rows has 0. so you should use a separate condition or a with clause that if one of them is zero the product should be zero.

Is there an aggregate function to multiply column values in mysql

If all your values are integers greater than zero, you can do:

round(exp(sum(log(column1))))

MySQL - Use GROUP BY and SUM to multiply two columns

As noted by patrick3853 and Olivier Depriester in the comments, the line SUM(jo.quantity * jo.price) should instead be SUM(jo.quantity) * SUM(jo.price).

The correct query is as follows:

SELECT p.name AS 'Project Name',
       SUM(jo.quantity) AS 'Job Order Quantity',
       SUM(jo.price) AS 'Job Order Price',
       SUM(jo.quantity) * SUM(jo.price) AS 'Cost'
FROM projects p
JOIN job_orders jo ON p.id = jo.project_id
GROUP BY  name;

sql server - multiply values by themselves

       select *
    into #a --*?
    FROM    ( SELECT    4 V
              UNION ALL
              SELECT    5
              UNION ALL
              SELECT    7
            ) Q

 select EXP(SUM(LOG(v))) As value from #a

SELECT  EXP(SUM(LOG(v)))
FROM    ( SELECT    4 V
          UNION ALL
          SELECT    5
          UNION ALL
          SELECT    7
        ) Q

Multiplication Aggregate Operator in Presto and AWS Athena

Turns out there's a super quick and easy hack! Harkening back to high school math class, given some {a₀, a₁, a₂, ..., a_n} where all of the a_i > 0 (THIS IS A BIG DEAL! We need all of the inputs to be positive, otherwise the natural logarithm is undefined!), we know that

exp(ln(a₀) + ln(a₁) + ... + ln(a_n)) = exp(ln(a₀))exp(ln(a₁))...exp(ln(a_n)) = a₀a₁a₂...a_n

where ln(x) is the natural logarithm of x and exp(x) is euler's number raised to the power x. But this is precisely what we want! Even better, presto has the functions exp(), sum(), and ln() built in, so all we have to do to is write

SELECT exp(sum(ln(column1))) AS product FROM yourtable;

and voila!

SQL Server Query - groupwise multiplication

Multiplying row values is the same as adding logarithms of row values

The trick is dealing with zeros and nulls.

Ok, checked now

DECLARE @foo TABLE (GrpID varchar(10), Value float)

INSERt @foo (GrpID, Value)
SELECT 'Grp1',          2
UNION ALL SELECT 'Grp1',          5
UNION ALL SELECT 'Grp1',          3
UNION ALL SELECT 'Grp2',          3
UNION ALL SELECT 'Grp2',         -5
UNION ALL SELECT 'Grp2',         -2
UNION ALL SELECT 'Grp3',          4
UNION ALL SELECT 'Grp3',          0
UNION ALL SELECT 'Grp3',          1
UNION ALL SELECT 'Grp4',         -2
UNION ALL SELECT 'Grp4',         -4
UNION ALL SELECT 'Grp5',          7
UNION ALL SELECT 'Grp5',       NULL
UNION ALL SELECT 'Grp6',       NULL
UNION ALL SELECT 'Grp6',       NULL
UNION ALL SELECT 'Grp7',         -1
UNION ALL SELECT 'Grp7',        10 

SELECT
    GrpID,
    CASE
       WHEN MinVal = 0 THEN 0
       WHEN Neg % 2 = 1 THEN -1 * EXP(ABSMult)
       ELSE EXP(ABSMult)
    END
FROM
    (
    SELECT
       GrpID, 
       --log of +ve row values
       SUM(LOG(ABS(NULLIF(Value, 0)))) AS ABSMult,
       --count of -ve values. Even = +ve result.
       SUM(SIGN(CASE WHEN Value < 0 THEN 1 ELSE 0 END)) AS Neg,
       --anything * zero = zero
       MIN(ABS(Value)) AS MinVal
    FROM
       @foo
    GROUP BY
       GrpID
    ) foo

Aggregate multiplicate function

Ideally, Access SQL would have a PRODUCT aggregate function available, but it doesn't. We can however simulate it by remembering what we learned about logarithms at school (or not...), and remembering that the anti-log of the sum of logs is equal to the product:

SELECT field2, EXP(Sum(LOG(Field3))) AS ProductOfField3
FROM t
GROUP BY Field2

Note that whereas a true PRODUCT function would simply return 0 for a group if there are any zero values, this solution will fail if there are any zero values, so watch out for that. Also, this approach won't work if there are any negative values.

To deal with zeroes we could do this:

SELECT
    field2, 
    EXP(Sum(LOG(IIf(Field3 = 0, 1, Field3)))) AS ProductOfField3,
    MIN(ABS(Field3)) AS MinOfAbsField3
FROM t
GROUP BY Field2

and then disregard the ProductOfField3 value for any row where MinOfAbsField3 is zero (as this indicates a group containing a zero, thus the 'true' product should be 0)

To deal with negative values we could further do this:

SELECT
    field2, 
    EXP(Sum(LOG(IIf(Field3 = 0, 1, ABS(Field3))))) AS ProductOfField3,
    MIN(ABS(Field3)) AS MinOfAbsField3,
    SUM(IIf(Field3 < 0, 1, 0)) AS SumOfNegativeIndicator
FROM t
GROUP BY Field2

and interpret the results with these rules:

If MinOfAbsField3 is zero, disregard ProductOfField3 for that row - the product is zero
Otherwise, the required answer for a given row is ProductOfField3, negated if SumOfNegativeIndicator is odd in that row

How to Multiply all values within a column with SQL like SUM()

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:

Output:

Col A Multi
-24

SQL Fiddle: http://sqlfiddle.com/#!3/01ddc/3/0

Multiplication Aggregate Operator in Sql