Product of column values
Why there is no PRODUCT function among SQL aggregate functions?
This question is frequently asked in professional social networks. The matter is about product of column values while grouping a table. Function like PRODUCT is absent from SQL Standard; and I don't know DBMS with support of this function. Good news is that we can express such function via three other functions which are supported by SQL servers. So.
Let's multiply values in value column of the following table:
The following logarithm property helps us: logarithm of product equals sum of logarithms. For our example this means
If we apply reverse function to natural logarithm (Ln), namely exponent (Exp), this gives
Thus, we can replace values product with expression at the right part in the equality above.
You can easyly verify the result in your brain or when using Excel :-).
The last solution is not universal. Because logarithm is not defined for the values less or equal zero, the query returns the following error:
An invalid floating point operation occurred.
Try this when running the query below.
Let's rewrite the solution for account of "impermissible" values with use of the following algorithm:
Here is solution with comments, which use this algorithm:
Notice the condition value <>0 at the last line of the query. Its presence is due to logarithm expression which is being evaluated by SQL Server despite of unprocessing of the corresponding branch of CASE operator when zeros occur. As a result the query returns the error instead of 0.
Smarties have asked yet: "What about NULLs?"
Really, our solution gives 0 in the case of presence of NULLs among values of a column.