Multiplication aggregate operator in SQL

Question

In SQL there are aggregation operators, like AVG, SUM, COUNT. Why doesn't it have an operator for multiplication? "MUL" or something.

I was wondering, does it exist for Oracle, MSSQL, MySQL ? If not is there a workaround that would give this behaviour?

Answer 1

Using CTE in MS SQL:

CREATE TABLE Foo(Id int, Val int)
INSERT INTO Foo VALUES(1, 2), (2, 3), (3, 4), (4, 5), (5, 6)

;WITH cte AS 
(
    SELECT Id, Val AS Multiply, row_number() over (order by Id) as rn
    FROM Foo
    WHERE Id=1
    UNION ALL
    SELECT ff.Id, cte.multiply*ff.Val as multiply, ff.rn FROM
    (SELECT f.Id, f.Val, (row_number() over (order by f.Id)) as rn
    FROM Foo f) ff
        INNER JOIN cte
        ON ff.rn -1= cte.rn
)
SELECT * FROM cte

Answer 2

You'll break any datatype fairly quickly as numbers mount up.

Using LOG/EXP is tricky because of numbers <= 0 that will fail when using LOG. I wrote a solution in this question that deals with this

Answer 3

No, but you can use Mathematics :)

if yourColumn is always bigger than zero:

select EXP(SUM(LOG(yourColumn))) As ColumnProduct from yourTable

Answer 4

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

Answer 5

I see an Oracle answer is still missing, so here it is:

SQL> with yourTable as
  2  ( select 1 yourColumn from dual union all
  3    select 2 from dual union all
  4    select 4 from dual union all
  5    select 8 from dual
  6  )
  7  select EXP(SUM(LN(yourColumn))) As ColumnProduct from yourTable
  8  /

COLUMNPRODUCT
-------------
           64

1 row selected.

Regards,
Rob.

Answer 6

With PostgreSQL, you can create your own aggregate functions, see http://www.postgresql.org/docs/8.2/interactive/sql-createaggregate.html

To create an aggregate function on MySQL, you'll need to build an .so (linux) or .dll (windows) file. An example is shown here: http://www.codeproject.com/KB/database/mygroupconcat.aspx

I'm not sure about mssql and oracle, but i bet they have options to create custom aggregates as well.