Is there an aggregate function to multiply column values in mysql

Question

I want to multiply column values within a table using an aggregate function in MYSQL similar to sum(column1) or avg(column1). I have tried product(column1) but the product function does not work. Kindly assist.

Please provide a few rows of sample data and the expected result. — The Impaler, Jun 08 '22 at 13:09
Just by multiplying a few rows you can get really big numbers and will run into data type overflows pretty quickly. I don't know if it makes sense to multiply more than a few rows. Please elaborate. — The Impaler, Jun 08 '22 at 13:12

score 2 · Accepted Answer · answered Jun 08 '22 at 13:14

2

If all your values are integers greater than zero, you can do:

round(exp(sum(log(column1))))

answered Jun 08 '22 at 13:14

ysth

96,171
6
121
214

This is what i wanted. is there any caveat if my values are in decimal and I remove `round`? – KMarto Jun 08 '22 at 13:19
1

it may introduce rounding errors; you'd have to try it on your data to see how well it does – ysth Jun 08 '22 at 13:20
for instance, if I do it on 1,2,3,4,5,6,7,8, I get 40320.00000000002 not 40320 – ysth Jun 08 '22 at 13:21
probably has to do with precision but I'm ok with 2 decimal points – KMarto Jun 08 '22 at 13:23

Is there an aggregate function to multiply column values in mysql

1 Answers1