mysql calculate multiplication with group by

Question

Please how to calculate multiplication with group by.

For example

table

id      value
1       1
1       2
2       1
2       2
2       3
3       0
3       3

I want next result using group by.

id      value
1       2
2       6
3       0

Thanks too much in advance.

Answer 1

Try this exp(sum(log(coalesce(the field you want to multiply,1))))

SELECT id, round( exp( sum( log( COALESCE ( value, 0 ) ) ) ) ) AS value FROM tests GROUP BY id;

Sorry but that will not work correctly if 0 value is present in column