Why is there no PRODUCT aggregate function in SQL?

Question

Im looking for something like SELECT PRODUCT(table.price) FROM table GROUP BY table.sale similar to how SUM works.

Have I missed something on the documentation, or is there really no PRODUCT function?

If so, why not?

Note: I looked for the function in postgres, mysql and mssql and found none so I assumed all sql does not support it.

Answer 1

For MSSQL you can use this. It can be adopted for other platforms: it's just maths and aggregates on logarithms.

SELECT
    GrpID,
    CASE
       WHEN MinVal = 0 THEN 0
       WHEN Neg % 2 = 1 THEN -1 * EXP(ABSMult)
       ELSE EXP(ABSMult)
    END
FROM
    (
    SELECT
       GrpID, 
       --log of +ve row values
       SUM(LOG(ABS(NULLIF(Value, 0)))) AS ABSMult,
       --count of -ve values. Even = +ve result.
       SUM(SIGN(CASE WHEN Value < 0 THEN 1 ELSE 0 END)) AS Neg,
       --anything * zero = zero
       MIN(ABS(Value)) AS MinVal
    FROM
       Mytable
    GROUP BY
       GrpID
    ) foo

Taken from my answer here: SQL Server Query - groupwise multiplication

Answer 2

I don't know why there isn't one, but (take more care over negative numbers) you can use logs and exponents to do:-

select exp (sum (ln (table.price))) from table ...

Answer 3

There is no PRODUCT set function in the SQL Standard. It would appear to be a worthy candidate, though (unlike, say, a CONCATENATE set function: it's not a good fit for SQL e.g. the resulting data type would involve multivalues and pose a problem as regards first normal form).

The SQL Standards aim to consolidate functionality across SQL products circa 1990 and to provide 'thought leadership' on future development. In short, they document what SQL does and what SQL should do. The absence of PRODUCT set function suggests that in 1990 no vendor though it worthy of inclusion and there has been no academic interest in introducing it into the Standard.

Of course, vendors always have sought to add their own functionality, these days usually as extentions to Standards rather than tangentally. I don't recall seeing a PRODUCT set function (or even demand for one) in any of the SQL products I've used.

In any case, the work around is fairly simple using log and exp scalar functions (and logic to handle negatives) with the SUM set function; see @gbn's answer for some sample code. I've never needed to do this in a business application, though.

In conclusion, my best guess is that there is no demand from SQL end users for a PRODUCT set function; further, that anyone with an academic interest would probably find the workaround acceptable (i.e. would not value the syntactic sugar a PRODUCT set function would provide).

Out of interest, there is indeed demand in SQL Server Land for new set functions but for those of the window function variety (and Standard SQL, too). For more details, including how to get involved in further driving demand, see Itzik Ben-Gan's blog.

Answer 4

You can perform a product aggregate function, but you have to do the maths yourself, like this...

SELECT
    Exp(Sum(IIf(Abs([Num])=0,0,Log(Abs([Num])))))*IIf(Min(Abs([Num]))=0,0,1)*(1-2*(Sum(IIf([Num]>=0,0,1)) Mod 2)) AS P
FROM
   Table1

Source: http://productfunctionsql.codeplex.com/

Answer 5

There is a neat trick in T-SQL (not sure if it's ANSI) that allows to concatenate string values from a set of rows into one variable. It looks like it works for multiplying as well:

declare @Floats as table (value float)
insert into @Floats values (0.9)
insert into @Floats values (0.9)
insert into @Floats values (0.9)

declare @multiplier float = null

select 
    @multiplier = isnull(@multiplier, '1') * value
from @Floats

select @multiplier

This can potentially be more numerically stable than the log/exp solution.

Answer 6

I think that is because no numbering system is able to accommodate many products. As databases are designed for large number of records, a product of 1000 numbers would be super massive and in case of floating point numbers, the propagated error would be huge.

Also note that using log can be a dangerous solution. Although mathematically log(a*b) = log(a)*log(b), it might not be in computers as we are not dealing with real numbers. If you calculate 2^(log(a)+log(b)) instead of a*b, you may get unexpected results. For example:

SELECT 9999999999*99999999974482, EXP(LOG(9999999999)+LOG(99999999974482))

in Sql Server returns

999999999644820000025518, 9.99999999644812E+23

So my point is when you are trying to do the product do it carefully and test is heavily.

Answer 7

The problem can be solved using modern SQL features such as window functions and CTEs. Everything is standard SQL and - unlike logarithm-based solutions - does not require switching from integer world to floating point world nor handling nonpositive numbers. Just number rows and evaluate product in recursive query until no row remain:

   with recursive t(c) as (
     select unnest(array[2,5,7,8])
   ), r(c,n) as (
     select t.c, row_number() over () from t
   ), p(c,n) as (
     select c, n from r where n = 1
     union all
     select r.c * p.c, r.n from p join r on p.n + 1 = r.n
   )
   select c from p where n = (select max(n) from p);

As your question involves grouping by sale column, things got little bit complicated but it's still solvable:

   with recursive t(sale,price) as (
     select 'multiplication', 2 union
     select 'multiplication', 5 union
     select 'multiplication', 7 union
     select 'multiplication', 8 union
     select 'trivial', 1 union
     select 'trivial', 8 union
     select 'negatives work', -2 union
     select 'negatives work', -3 union
     select 'negatives work', -5 union
     select 'look ma, zero works too!', 1 union
     select 'look ma, zero works too!', 0 union
     select 'look ma, zero works too!', 2
   ), r(sale,price,n,maxn) as (
     select t.sale, t.price, row_number() over (partition by sale), count(1) over (partition by sale)
     from t
   ), p(sale,price,n,maxn) as (
     select sale, price, n, maxn
     from r where n = 1
     union all
     select p.sale, r.price * p.price, r.n, r.maxn
     from p
     join r on p.sale = r.sale and p.n + 1 = r.n
   )
   select sale, price
   from p
   where n = maxn
   order by sale;

Result:

sale,price
"look ma, zero works too!",0
multiplication,560
negatives work,-30
trivial,8

Tested on Postgres.

Answer 8

Another approach based on fact that the cardinality of cartesian product is product of cardinalities of particular sets ;-)

⚠ WARNING: This example is just for fun and is rather academic, don't use it in production! (apart from the fact it's just for positive and practically small integers)⚠

with recursive t(c) as (
  select unnest(array[2,5,7,8])
), p(a) as (
  select array_agg(c) from t
  union all
  select p.a[2:]
  from p
  cross join generate_series(1, p.a[1])
)
select count(*) from p where cardinality(a) = 0;

Answer 9

One way to deal with this problem (if you are working in a scripting language) is to use the group_concat function. For example, SELECT group_concat(table.price) FROM table GROUP BY table.sale

This will return a string with all prices for the same sale value, separated by a comma. Then with a parser you can get each price, and do a multiplication. (In php you can even use the array_reduce function, in fact in the php.net manual you get a suitable example).

Cheers

Answer 10

Here is an oracle solution for anyone who needs it

with data(id, val) as(
select 1,1.0 from dual union all
select 2,-2.0 from dual union all
select 3,1.0 from dual union all
select 4,2.0 from dual 
),
neg(val , modifier) as(
select exp(sum(ln(abs(val)))), case when mod(count(*),2) = 0 then 1 Else -1 end
from data
where val <0
)
,
pos(val) as (
select exp(sum(ln(val)))
from data
where val >=0
)
select (select val*modifier from neg)*(select val from pos) product from dual