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:
Thus, we can replace values product with expression at the right part in the equality above. All that we need to do to solve task is to write this formula in SQL language, taking into account that values are in the value column.
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. Let's follow common logic of behaviour of aggregate functions - to ignore NULLs when computing. Below is final solution, which has single difference in comparison with the previous one. Who will guess which is this?
Console
Execute
WITH T AS(SELECT * FROM(VALUES(-2),(-3),(4),(-5), (NULL)) X(value)),
P AS(
SELECTSUM(CASEWHEN value<0THEN1ELSE0END) neg, -- number of negative values
SUM(CASEWHEN value>0THEN1ELSE0END) pos, -- number of positive values
COUNT(value) total -- total number of values
FROM T)
SELECTCASEWHEN total <> pos+neg /* there are zeros */THEN0ELSE