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

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