I don't know if there is way to define a new aggregate function, not without messing with MySQL source code.
But if your numbers are all positive, you may well derive from the arithmetic identity:
log( product( Ai ) ) = sum( log( Ai ) )
that you can use EXP(SUM(LOG(x)))
to calculate PRODUCT(x)
. Test in SQL-Fiddle:
SELECT EXP(SUM(LOG(a))) AS product
FROM t ;
SELECT col, EXP(SUM(LOG(a))) AS product
FROM t
GROUP BY col ;
When the data can have 0s, it gets a bit more complicated:
SELECT (NOT EXISTS (SELECT 1 FROM t WHERE a = 0))
* EXP(SUM(LOG(a))) AS p
FROM t
WHERE a > 0 ;
SELECT d.col,
(NOT EXISTS (SELECT 1 FROM t AS ti WHERE ti.col = d.col AND ti.a = 0))
* COALESCE(EXP(SUM(LOG(t.a))),1) AS p
FROM
( SELECT DISTINCT col
FROM t
) AS d
LEFT JOIN
t ON t.col = d.col
AND t.a > 0
GROUP BY d.col ;
Tested at SQL-Fiddle
For other DBMS, that do not have MySQL's auto-conversion of boolean values to integers, the
(NOT EXISTS (SELECT ...))
should be replaced with:
(CASE WHEN EXISTS (SELECT 1...) THEN 0 ELSE 1 END)
Specifically for Oracle, a few more changes will be needed, without changing the logic of the answer, only because Oracle does not follow strict ANSI standard in some areas. Tested at SQL-Fiddle-2