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