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.
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
- 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 0and
-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.
% 2against 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
- sign(data) returns