Joe Celko's SQL for Smarties, 3rd Edition

21.6. The PRD() Aggregate Function

Bob McGowan sent me a message on CompuServe asking for help with a problem. His client, a financial institution, tracks investment performance with a table something like this:

CREATE TABLE Performance
(portfolio_id CHAR(7) NOT NULL,
 execute_date DATE NOT NULL,
 rate_of_return DECIMAL(13,7) NOT NULL);

To calculate a rate of return over a date range, you use the formula:

 (1 + rate_of_return [day_1])
* (1 + rate_of_return [day_2])
* (1 + rate_of_return [day_3])
* (1 + rate_of_return [day_4])
 ...
* (1 + rate_of_return [day_N])

How would you construct a query that would return one row for each portfolio’s return over the date range? What Mr. McGowan really wants is an aggregate function in the SELECT clause to ...