/* This script creates and populates the tables needed for the examples in the Special
Means Part 2 article */
CREATE TABLE tblGeomMean (ID int IDENTITY(1, 1), Investment varchar(20), Period int,
AnnualReturn decimal(9, 6))
INSERT INTO tblGeomMean (Investment, Period, AnnualReturn) VALUES ('A', 1, 0.0619)
INSERT INTO tblGeomMean (Investment, Period, AnnualReturn) VALUES ('A', 2, 0.0468)
INSERT INTO tblGeomMean (Investment, Period, AnnualReturn) VALUES ('A', 3, -0.0283)
INSERT INTO tblGeomMean (Investment, Period, AnnualReturn) VALUES ('A', 4, -0.1096)
INSERT INTO tblGeomMean (Investment, Period, AnnualReturn) VALUES ('A', 5, 0.0212)
INSERT INTO tblGeomMean (Investment, Period, AnnualReturn) VALUES ('A', 6, -0.0721)
INSERT INTO tblGeomMean (Investment, Period, AnnualReturn) VALUES ('A', 7, -0.0443)
INSERT INTO tblGeomMean (Investment, Period, AnnualReturn) VALUES ('A', 8, 0.0117)
INSERT INTO tblGeomMean (Investment, Period, AnnualReturn) VALUES ('A', 9, 0.0723)
INSERT INTO tblGeomMean (Investment, Period, AnnualReturn) VALUES ('A', 10, 0.0166)
INSERT INTO tblGeomMean (Investment, Period, AnnualReturn) VALUES ('B', 1, 0.0342)
INSERT INTO tblGeomMean (Investment, Period, AnnualReturn) VALUES ('B', 2, 0.0721)
INSERT INTO tblGeomMean (Investment, Period, AnnualReturn) VALUES ('B', 3, -0.03)
INSERT INTO tblGeomMean (Investment, Period, AnnualReturn) VALUES ('B', 4, 0.076)
INSERT INTO tblGeomMean (Investment, Period, AnnualReturn) VALUES ('B', 5, 0.0612)
INSERT INTO tblGeomMean (Investment, Period, AnnualReturn) VALUES ('B', 6, 0.0742)
INSERT INTO tblGeomMean (Investment, Period, AnnualReturn) VALUES ('B', 7, 0.0175)
INSERT INTO tblGeomMean (Investment, Period, AnnualReturn) VALUES ('B', 8, 0.0959)
INSERT INTO tblGeomMean (Investment, Period, AnnualReturn) VALUES ('B', 9, 0.097)
INSERT INTO tblGeomMean (Investment, Period, AnnualReturn) VALUES ('B', 10, 0.0039)
CREATE TABLE tblWeightedGeomMean (ID int IDENTITY(1, 1), Investment varchar(20), Segment int,
Years int, AnnualReturn decimal(9, 6))
INSERT INTO tblWeightedGeomMean (Investment, Segment, Years, AnnualReturn) VALUES ('A', 1, 1, 0.2382)
INSERT INTO tblWeightedGeomMean (Investment, Segment, Years, AnnualReturn) VALUES ('A', 2, 2, -0.0946)
INSERT INTO tblWeightedGeomMean (Investment, Segment, Years, AnnualReturn) VALUES ('A', 3, 3, 0.281)
INSERT INTO tblWeightedGeomMean (Investment, Segment, Years, AnnualReturn) VALUES ('A', 4, 4, -0.1071)
INSERT INTO tblWeightedGeomMean (Investment, Segment, Years, AnnualReturn) VALUES ('B', 1, 2, 0.1327)
INSERT INTO tblWeightedGeomMean (Investment, Segment, Years, AnnualReturn) VALUES ('B', 2, 5, 0.0121)
INSERT INTO tblWeightedGeomMean (Investment, Segment, Years, AnnualReturn) VALUES ('B', 3, 3, -0.3234)
INSERT INTO tblWeightedGeomMean (Investment, Segment, Years, AnnualReturn) VALUES ('B', 4, 4, -0.0773)
INSERT INTO tblWeightedGeomMean (Investment, Segment, Years, AnnualReturn) VALUES ('B', 5, 3, 0.1484)
INSERT INTO tblWeightedGeomMean (Investment, Segment, Years, AnnualReturn) VALUES ('B', 6, 2, -0.1648)
INSERT INTO tblWeightedGeomMean (Investment, Segment, Years, AnnualReturn) VALUES ('B', 7, 2, 0.0149)
INSERT INTO tblWeightedGeomMean (Investment, Segment, Years, AnnualReturn) VALUES ('B', 8, 4, 0.0458)
INSERT INTO tblWeightedGeomMean (Investment, Segment, Years, AnnualReturn) VALUES ('B', 9, 5, 0.0932)
INSERT INTO tblWeightedGeomMean (Investment, Segment, Years, AnnualReturn) VALUES ('B', 10, 2, 0.0031)
CREATE TABLE tblQuadMean (ID int IDENTITY(1, 1), Series varchar(20), Point int, Theory decimal(12, 6),
Observed decimal(12, 6))
INSERT INTO tblQuadMean (Series, Point, Theory, Observed) VALUES ('A', 1, 11, -3)
INSERT INTO tblQuadMean (Series, Point, Theory, Observed) VALUES ('A', 2, 16, 19)
INSERT INTO tblQuadMean (Series, Point, Theory, Observed) VALUES ('A', 3, 19, 18)
INSERT INTO tblQuadMean (Series, Point, Theory, Observed) VALUES ('A', 4, 20, 21)
INSERT INTO tblQuadMean (Series, Point, Theory, Observed) VALUES ('A', 5, 19, 14)
INSERT INTO tblQuadMean (Series, Point, Theory, Observed) VALUES ('A', 6, 16, -4)
INSERT INTO tblQuadMean (Series, Point, Theory, Observed) VALUES ('A', 7, 11, 17)
INSERT INTO tblQuadMean (Series, Point, Theory, Observed) VALUES ('A', 8, 4, 13)
INSERT INTO tblQuadMean (Series, Point, Theory, Observed) VALUES ('A', 9, -5, 0)
INSERT INTO tblQuadMean (Series, Point, Theory, Observed) VALUES ('A', 10, -16, -19)
INSERT INTO tblQuadMean (Series, Point, Theory, Observed) VALUES ('B', 1, 3, 5)
INSERT INTO tblQuadMean (Series, Point, Theory, Observed) VALUES ('B', 2, 4, 15)
INSERT INTO tblQuadMean (Series, Point, Theory, Observed) VALUES ('B', 3, 7, 18)
INSERT INTO tblQuadMean (Series, Point, Theory, Observed) VALUES ('B', 4, 12, 18)
INSERT INTO tblQuadMean (Series, Point, Theory, Observed) VALUES ('B', 5, 19, 17)
INSERT INTO tblQuadMean (Series, Point, Theory, Observed) VALUES ('B', 6, 28, 29)
INSERT INTO tblQuadMean (Series, Point, Theory, Observed) VALUES ('B', 7, 39, 32)
INSERT INTO tblQuadMean (Series, Point, Theory, Observed) VALUES ('B', 8, 52, 47)
INSERT INTO tblQuadMean (Series, Point, Theory, Observed) VALUES ('B', 9, 67, 65)
INSERT INTO tblQuadMean (Series, Point, Theory, Observed) VALUES ('B', 10, 84, 94)
SELECT {group by columns if desired,} CASE
WHEN MIN([ValueColumn]) > 0 THEN EXP(AVG(LOG(
CASE WHEN [ValueColumn] > 0 THEN [ValueColumn] ELSE 1 END)))
ELSE NULL END AS GeomMean
FROM {tables}
{GROUP BY {group by columns}}
SELECT {group by columns if desired,} CASE
WHEN MIN([ValueColumn]) > -1 THEN EXP(AVG(LOG(1 +
(CASE WHEN [ValueColumn] > -1 THEN [ValueColumn] ELSE 1 END)))) - 1
ELSE NULL END AS GeomMean
FROM {tables}
{GROUP BY {group by columns}}
SELECT Investment, CASE
WHEN MIN([AnnualReturn]) > -1 THEN EXP(AVG(LOG(1 + (CASE
WHEN [AnnualReturn] > -1 THEN [AnnualReturn] ELSE 1 END)))) - 1
ELSE NULL END AS CAGR
FROM tblGeomMean
GROUP BY Investment;
SELECT {group by columns if desired,} CASE
WHEN MIN([WeightColumn]) >= 0 AND MAX([WeightColumn]) > 0
AND MIN([ValueColumn]) > 0 THEN
EXP(SUM([WeightColumn] * LOG(CASE WHEN [ValueColumn] > 0 THEN [ValueColumn] ELSE 1 END))) /
SUM([WeightColumn]))
ELSE NULL END AS WtdGeomMean
FROM {tables}
{GROUP BY {group by columns}}
SELECT {group by columns if desired,} CASE
WHEN MIN([WeightColumn]) >= 0 AND MAX([WeightColumn]) > 0
AND MIN([ValueColumn]) > -1 THEN
EXP(SUM([WeightColumn] * LOG(1 + CASE WHEN [ValueColumn] > -1 THEN [ValueColumn] ELSE 1 END))) /
SUM([WeightColumn])) - 1
ELSE NULL END AS WtdGeomMean
FROM {tables}
{GROUP BY {group by columns}}
SELECT Investment, CASE
WHEN MIN([Years]) >= 0 And MAX([Years])>0 And MIN([AnnualReturn]) > -1 THEN
EXP(SUM([Years] * LOG(1 + CASE
WHEN [AnnualReturn] > -1 THEN [AnnualReturn]
ELSE 1 END)) / SUM([Years])) - 1
ELSE Null END AS CAGR
FROM tblWeightedGeomMean
GROUP BY Investment;
SELECT {group by columns if desired,} POWER(AVG(POWER([ValueColumn], 2)), 0.5) AS QuadMean
FROM {tables}
{GROUP BY {group by columns}}
SELECT Series, POWER(AVG(POWER([Theory] - [Observed], 2)), 0.5) AS QuadMean
FROM tblQuadMean
GROUP BY Series;
SELECT {group by columns if desired,} POWER(AVG(POWER([ValueColumn], p)), (1 / p)) AS GeneralMean
FROM {tables}
{GROUP BY {group by columns}}
SELECT {group by columns if desired,} POWER((CASE
WHEN MIN([WeightColumn]) >= 0 AND MAX([WeightColumn]) > 0 THEN
Sum([WeightColumn] * POWER([ValueColumn], p))
ELSE NULL END) / SUM([WeightColumn]), (1 / p)) AS WtdGeneralMean
FROM {tables}
{GROUP BY {group by columns}}
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)