CODEWITHSUNDEEP
rpostgresqlsumamazon-redshiftdbplyr
Cyrus Mohammadian
Cyrus Mohammadian

Reputation: 5193

R prod() function but in SQL, how to take product of a group by

In R, taking the product of a group by can be undertaken like:

 iris %>% group_by(Species) %>% summarize(new_col = prod(Petal.Length))

How can I achieve that same concept in either postgresql or dbplyr/dplyr?

Upvotes: 1

Views: 162

Answers (1)

GMB
GMB

Reputation: 222632

Unfortunately the SQL standard does not define an aggregate "product" function. You can, however, work around this with arithmetics.

Say that you want to compute the product of petal_length in groups of rows sharing the same species in table mytable:

select species, exp(sum(ln(petal_length))) petal_length_product
from mytable
group by species

This works as long as all values of petal_length are greater than 0.

Upvotes: 6

Related Questions