Difference between revisions of "Multiply sets"

From SQLZOO
Jump to: navigation, search
Line 12: Line 12:
 
</source>
 
</source>
 
<div>
 
<div>
In this example we are attempting to see how much money has been obtained over 4
+
<p>In this example we are attempting to see how much money has been obtained over 4
years due to interest.
+
years due to interest.</p>
As the added up rate is not accurate 5 + 4 + 5 + 3 = 17%
+
<p>As the added up rate is not accurate 5 + 4 + 5 + 3 = 17%</p>
We need to instead find the logarithm of the compound interest and then we need to sum that.
+
<p>We need to instead find the logarithm of the compound interest and then we need to sum that.</p>
SELECT SUM(LN((rate/100)+1)) FROM interest
+
<p>SELECT SUM(LN((rate/100)+1)) FROM interest</p>
Then we inverse or take the exponent of the logarithm with
+
<p>Then we inverse or take the exponent of the logarithm with</p>
SELECT EXP(SUM(LN((rate/100)+1))) FROM interest
+
<p>SELECT EXP(SUM(LN((rate/100)+1))) FROM interest</p>
and then finally to get the amount after 4 years we times this amount by 100 ($100).
+
<p>and then finally to get the amount after 4 years we times this amount by 100 ($100).</p>
 
</div>
 
</div>
 
<source lang='sql' class='def'>SELECT EXP(SUM(LN((rate/100)+1)))*100
 
<source lang='sql' class='def'>SELECT EXP(SUM(LN((rate/100)+1)))*100

Revision as of 12:59, 30 July 2012

Multiply across a result set.

schema:scott
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);

In this example we are attempting to see how much money has been obtained over 4 years due to interest.

As the added up rate is not accurate 5 + 4 + 5 + 3 = 17%

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
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense