Adding monthly percentage changes does not calculate compound growth. The PRODUCT aggregate in SQL Server 2025 lets you multiply factors across rows without building a logarithm workaround.
Multiply Factors Rather Than Percentage Labels
A rate describes a change relative to a starting value. To compound consecutive rates, convert each rate into a factor by adding one. Multiply those factors, then subtract one from the combined factor for the total proportional change. Store rates as fractions, with a clear documented convention.
I check the rate convention before reading the aggregate expression. A value representing three percent should not silently become a factor based on three whole units. Column names help, but data validation settles the question. A misplaced decimal point remains very productive in the wrong direction.
The following values are invented inputs for a calculation example. They are not observed investment returns or a prediction. Run the setup and later queries in the same SSMS session on SQL Server 2025. The compound calculation assumes consecutive periods without external additions or withdrawals. Store one row for each series and period, as the primary key enforces here. Load corrections by replacing the intended period value through your normal transaction process. Appending another copy of the same period changes the factor chain. A successful aggregate cannot distinguish a legitimate repeated rate from a duplicated source row.
CREATE TABLE #MonthlyRates
(
SeriesID int NOT NULL,
MonthNumber int NOT NULL,
RateFraction decimal(9,6) NOT NULL,
PRIMARY KEY (SeriesID, MonthNumber),
CHECK (RateFraction >= -1)
);
INSERT #MonthlyRates (SeriesID, MonthNumber, RateFraction)
VALUES (1, 1, 0.030000), (1, 2, -0.020000),
(1, 3, 0.015000), (2, 1, 0.010000),
(2, 2, 0.005000), (2, 3, -0.010000);Calculate One PRODUCT Aggregate for Each Series
GROUP BY keeps separate series independent. Multiply the factor expression within each group. The query also returns period counts and summed rates for comparison. The summed-rate column is useful as a contrast, but it should not replace the compounded calculation.
SELECT SeriesID, COUNT_BIG(*) AS PeriodCount,
SUM(RateFraction) AS SumOfRates,
PRODUCT(1 + RateFraction) AS GrowthFactor,
PRODUCT(1 + RateFraction) - 1 AS CompoundRate
FROM #MonthlyRates
GROUP BY SeriesID
ORDER BY SeriesID;Check the returned factors and the period coverage against your input contract. Missing periods need an explicit decision. A missing rate is different from a recorded rate of zero. If a period includes external cash movements, this simple factor chain does not by itself define the appropriate business return calculation.
Repeat the PRODUCT Aggregate With a Window
The PRODUCT aggregate with OVER preserves individual rows while calculating across the partition. That is useful when each detail row needs its group's combined factor. The partition key plays the same grouping role, but the result still contains the monthly detail instead of one row per series.
SELECT SeriesID, MonthNumber, RateFraction,
PRODUCT(1 + RateFraction)
OVER (PARTITION BY SeriesID) AS FullSeriesFactor,
PRODUCT(1 + RateFraction)
OVER (PARTITION BY SeriesID ORDER BY MonthNumber) AS ThroughMonthFactor
FROM #MonthlyRates
ORDER BY SeriesID, MonthNumber;Adding ORDER BY inside OVER creates the cumulative version. The primary key makes MonthNumber unique within each series in this example. With repeated ordering values, peers require careful interpretation. Resolve the intended business sequence before treating any displayed cumulative value as a period-end result.
Use Probabilities Only With Independence
For independent events, the probability that all events occur is the product of their probabilities. Validate each input between zero and one. Also validate the independence assumption outside the database. A syntactically correct multiplication does not establish that two real events are independent.
CREATE TABLE #EventProbabilities
(
ScenarioID int NOT NULL,
EventID int NOT NULL,
ProbabilityValue float NOT NULL,
PRIMARY KEY (ScenarioID, EventID),
CHECK (ProbabilityValue >= 0 AND ProbabilityValue <= 1)
);
INSERT #EventProbabilities VALUES
(1, 1, 0.8), (1, 2, 0.7), (1, 3, 0.9),
(2, 1, 0.4), (2, 2, 0.0);
SELECT ScenarioID,
PRODUCT(ProbabilityValue) AS AllEventsProbability
FROM #EventProbabilities
GROUP BY ScenarioID;These are hand-selected demonstration probabilities. Dependent events require an appropriate conditional model instead of this independent-event shortcut. Long probability chains also become very small. Choose numeric storage and output precision for the range you actually need, and verify underflow behavior during representative tests.
How the PRODUCT Aggregate Treats NULL and Zero
The aggregate ignores NULL inputs. A zero participates in multiplication and makes the mathematical product zero. Those rules answer different questions. Ignoring an unknown probability can produce a result for the known subset that looks complete unless you expose the missing input count.
SELECT COUNT_BIG(*) AS InputCount,
COUNT(FactorValue) AS KnownFactorCount,
PRODUCT(FactorValue) AS CombinedFactor
FROM (VALUES (CAST(2 AS decimal(12,4))),
(CAST(NULL AS decimal(12,4))),
(CAST(0 AS decimal(12,4)))) AS v(FactorValue);
SELECT PRODUCT(FactorValue) AS AllNullProduct
FROM (VALUES (CAST(NULL AS float)),
(CAST(NULL AS float))) AS v(FactorValue);Inspect the all-NULL result separately from the mixed-input result. Do not replace unknown values with one unless the business rule explicitly treats them as a neutral factor. Replacing them with zero imposes a different rule. Preserve missingness in the report so the reader can judge whether the product is usable.
Leave the Logarithm Trick to Positive Inputs
Before this built-in function, a common workaround used EXP(SUM(LOG(x))). The logarithm identity works for positive factors, subject to floating-point limitations. LOG does not accept zero or negative real inputs. Feed the old trick a zero and SQL Server stops with error 3623, an invalid floating point operation. Adding a NULLIF guard for zero makes the aggregate skip that zero, which changes the intended product.
SELECT PRODUCT(FactorValue) AS NativeProduct,
EXP(SUM(LOG(FactorValue))) AS PositiveLogProduct
FROM (VALUES (CAST(1.03 AS float)),
(CAST(0.98 AS float)),
(CAST(1.015 AS float))) AS v(FactorValue);
SELECT PRODUCT(FactorValue) AS SignedProduct
FROM (VALUES (CAST(-2 AS decimal(12,4))),
(CAST(3 AS decimal(12,4))),
(CAST(-4 AS decimal(12,4)))) AS v(FactorValue);The second query uses signed factors directly. Their signs are valid for general multiplication, even though they are invalid probability inputs. Supporting signs in a logarithm workaround requires separate sign and zero logic. A direct aggregate expresses that operation with much less room for accidental omissions.
Choose Numeric Types Before Aggregating
I inspect the expression's type before trusting a large factor chain. Integer inputs return integer products and can overflow. Decimal inputs with nonzero scale return decimal(38,6), while scale-zero decimal inputs return decimal(38,0). Casting only the finished result cannot rescue an overflow inside the aggregate.
Cast the input expression when a different numeric category is needed. Floating-point inputs return float and carry approximate arithmetic. Very small products, very large products, and repeated fractional multiplication deserve boundary tests. A wide display format does not add precision that the calculation never retained.
Avoid PRODUCT(DISTINCT …) for sequential periods unless duplicate factor values truly need removal. Two different months with the same rate both belong in a compound chain. Deduplicating their numeric values removes a legitimate period. Deduplicate erroneous source rows using their business keys before aggregation instead.
Validate Coverage Alongside the Calculation
Which missing factor would make your result look more convincing than it deserves? Return known-input counts and expected period coverage with the final value. Check duplicates, units, and valid ranges before presenting the aggregate. A compact formula should not hide the data contract underneath it.
Use the PRODUCT aggregate when row-wise multiplication is the actual operation. Keep the examples small while testing sign, zero, and missing-value behavior. Then validate representative chains using your required precision. That gives the new syntax a sound mathematical and data-quality basis.
Related reading on this blog: Percent of Total With SUM() OVER() in One Query and Why FLOAT Math Does Not Add Up in T-SQL Queries.
A product is not a complete data check, it is multiplication over the factors you supplied.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
