loading..
   English
23:47

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:

Console
Execute
  1. SELECT value FROM (
  2. VALUES(2),(3),(4),(5)
  3. ) X(value);

value
2
3
4
5

The following logarithm property helps us: logarithm of product equals sum of logarithms. For our example this means

  1. ln(2*3*4*5) = ln(2) + ln(3) + ln(4) + ln(5)

If we apply reverse function to natural logarithm (Ln), namely exponent (Exp), this gives

  1. exp(ln(2*3*4*5)) = 2*3*4*5 = exp(ln(2) + ln(3) + ln(4) + ln(5))

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.

Console
Execute
  1. SELECT exp(SUM(log(value))) product FROM (
  2. VALUES(2),(3),(4),(5)
  3. ) X(value);

product
120

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.

Console
Execute
  1. SELECT exp(SUM(log(value))) product FROM (
  2. VALUES(2),(-3),(4),(-5)
  3. ) X(value);

Let's rewrite the solution for account of "impermissible" values with use of the following algorithm:

  1. If there are zeros  among calculated values, the result equals 0.
  2. If the number of negative values is odd, the product of absolute values of the column has to be multiplying by (-1).
  3. If the number of negative values is even, the result equals the product of absolute values of the column.

Here is solution with comments, which use this algorithm:

Console
Execute
  1. WITH T AS(SELECT * FROM (VALUES(-2),(-3),(4),(-5)) X(value)),
  2. P AS (
  3. SELECT SUM(CASE WHEN value<0 THEN 1 ELSE 0 END) neg, -- number of negative values
  4. SUM(CASE WHEN value>0 THEN 1 ELSE 0 END) pos, -- number of positive values
  5. COUNT(*) total -- total number of values
  6. FROM T)
  7. SELECT CASE WHEN total <> pos+neg /* there are zeros */ THEN 0 ELSE
  8. (CASE WHEN neg%2=1 THEN -1 ELSE +1 END) *exp(SUM(log(abs(value))))
  9. END product
  10. FROM T,P
  11. WHERE value <> 0
  12. GROUP BY neg, pos, total;

product
-120

Notice the condition value <>0 at the last line of the query. Its presence is due to logarithm expression which is being evaluated by  A database management system (DBMS) by Microsoft Corporation.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
  1. WITH T AS(SELECT * FROM (VALUES(-2),(-3),(4),(-5), (NULL) ) X(value)),
  2. P AS (
  3. SELECT SUM(CASE WHEN value<0 THEN 1 ELSE 0 END) neg, -- number of negative values
  4. SUM(CASE WHEN value>0 THEN 1 ELSE 0 END) pos, -- number of positive values
  5. COUNT(value) total -- total number of values
  6. FROM T)
  7. SELECT CASE WHEN total <> pos+neg /* there are zeros */ THEN 0 ELSE
  8. (CASE WHEN neg%2=1 THEN -1 ELSE +1 END) *exp(SUM(log(abs(value))))
  9. END
  10. product FROM T,P WHERE value <> 0 GROUP BY neg, pos, total;


Bookmark and Share
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100