Multiply sets
Multiplying across a result set allows for interest rates to calculated correctly.
In this example we get the interest after 4 years imagine over the 4 years we have rates 5%, 4%, 5% and 3% adding these rates to get 17% (£117) isn't correct.
To get the correct results you have to follow the steps given here.
DROP TABLE interest
CREATE TABLE interest(
yr INTEGER,
rate INTEGER );
INSERT INTO interest VALUES (2002,5);
INSERT INTO interest VALUES (2003,4);
INSERT INTO interest VALUES (2004,5);
INSERT INTO interest VALUES (2005,3);
We need to instead find the logarithm of the compound interest and then we need to sum that.
SELECT SUM(LN((rate/100)+1)) FROM interest
Then we inverse or take the exponent of the logarithm with
SELECT EXP(SUM(LN((rate/100)+1))) FROM interest
and then finally to get the amount after 4 years we times this amount by 100 (£100).
SELECT EXP(SUM(LN((rate/100)+1)))*100
FROM interest
Hack 10 Converting subqueries into joins
Hack 11 Converting aggregate subqueries into joins
Hack 16 Search for a String across columns
Hack 24 Multiply Across a Result Set
Hack 25.5 Splitting and combining columns
Hack 26 Include the rows your JOIN forgot
Hack 30 Calculate the maximum/minimum of two fields
Hack 33 Get values and subtotals in one shot
Hack 50 Combine tables containing different data
Hack 51/52 Display rows as columns
Hack 55 Import Someone Else's Data
Hack 62 Issue Queries Without Using a Table
Hack 63 Generate rows without tables
Hack 72 Extract a subset of the results
Hack 78 Break it down by Range
Hack 88 Test two values from a subquery
