CODEWITHSUNDEEP
rsqliteaggregateproduct
Richard Herron
Richard Herron

Reputation: 10112

Is there a product operator (or work around) in SQLite?

I am using GROUP BY to aggregate over an item per month. I would like the SUM of one column and the product of another. I can't find a product operator online or in my book... but is there a series of other operators that will build the product operator?

In a pinch, I can do the product operating in R, which is where the data are heading, but I would love to minimize the amount of looping I do.

Thanks!

Update: OK, so I really wanted to create a product aggregator to send the product of a column back to R. In trying to answer a futerh downstream question I got the answer that put it all together. There's already a library of common functions not incorporated into SQLite (the Healey functions). I can easily use these functions in R using a library (RSQLite.extfuns). So I can create the product aggregator by exponentiating the sum of logs. Thanks to DWin for showing me the key piece about the RQLite.extfuns library.

Upvotes: 2

Views: 1216

Answers (3)

mclements
mclements

Reputation: 16

This is an old question. An alternative workaround is to use logs and exponentials, where product(y)=exp(sum(ln(y))). As SQL:

with
  d as (select 1 as x, 3 as y union all select 1 as x, 4 as y)
  select x, sum(y) as sum_y, exp(sum(ln(y))) as prod_y from d group by x;

1|7|12.0

Upvotes: 0

Doug Currie
Doug Currie

Reputation: 41210

If the number of rows per group is small enough, you might use group-concat to return all the multiplicands for R.

Upvotes: 2

Robert Harvey
Robert Harvey

Reputation: 180878

You can add your own aggregate operator using create_function().

Upvotes: 3

Related Questions