Log in
This blog post explores the new PRODUCT function in SQL Server 2025, which calculates the product of a set of numeric values — similar to how SUM and AVG work for addition and averaging, but for multiplication.
Prior to SQL Server 2025, SQL Server lacked a built-in way to compute the product of values in a set. You had to use workarounds like looping or user-defined aggregates. With PRODUCT, this is now a simple one-line expression.
PRODUCT supports both aggregate and analytic (windowed) forms and works with both ALL values (default) and DISTINCT values. Nulls are ignored, and the function is compatible with all numeric types except bit.
Compute Product of Prices for Each Product
The first example illustrates how to use the new PRODUCT aggregate function in SQL Server 2025 to calculate the cumulative product of prices for each product across multiple orders. It also shows how to compute the product considering only distinct price values.
CREATE TABLE OrderDetail ( OrderId int, ProductId int, Price decimal(10, 4))INSERT INTO OrderDetail (OrderId, ProductId, Price) VALUES (1, 101, 136.87), (1, 102, 29.57), (1, 103, 396.85), (2, 101, 136.87), (2, 102, 29.57), (3, 101, 136.87), (3, 102, 29.57), (4, 101, 149.22), (4, 102, 29.57)-- Compute product of all prices and distinct prices for each ProductIdSELECT ProductId, ProductOfPrices = PRODUCT(Price), ProductOfDistinctPrices = PRODUCT(DISTINCT Price)FROM OrderDetailGROUP BY ProductId
Result:
| ProductId | ProductOfPrices | ProductOfDistinctPrices |
|---|---|---|
| 101 | 382606053.829162 | 20423.741400 |
| 102 | 764548.953348 | 29.570000 |
| 103 | 396.850000 | 396.850000 |
Alternative using OVER (PARTITION BY ...)
This version computes the product for each row using a windowed aggregate (that is, using OVER rather than GROUP BY). This allows you to retain the detail rows (which were lost in the previous GROUP BY query) while also showing the total product per partition.
SELECT ProductId, OrderId, Price, ProductOfPrices = PRODUCT(Price) OVER (PARTITION BY ProductId)FROM OrderDetailORDER BY ProductId, OrderId
Result:
| ProductId | OrderId | Price | ProductOfPrices |
|---|---|---|---|
| 101 | 1 | 136.8700 | 382606053.829162 |
| 101 | 2 | 136.8700 | 382606053.829162 |
| 101 | 3 | 136.8700 | 382606053.829162 |
| 101 | 4 | 149.2200 | 382606053.829162 |
| 102 | 1 | 29.5700 | 764548.953348 |
| 102 | 2 | 29.5700 | 764548.953348 |
| 102 | 3 | 29.5700 | 764548.953348 |
| 102 | 4 | 29.5700 | 764548.953348 |
| 103 | 1 | 396.8500 | 396.850000 |
Compounded Return from Periodic Rates
The next example uses PRODUCT to compute the compounded return for financial instruments over multiple time periods.
CREATE TABLE Instrument ( InstrumentId varchar(10), Period tinyint, RateOfReturn decimal(10, 4))INSERT INTO Instrument (InstrumentId, Period, RateOfReturn) VALUES ('BOND1', 1, 0.035), ('BOND1', 2, 0.0275), ('BOND1', 3, 0.0325), ('ETF1', 1, 0.08), ('ETF1', 2, -0.045), ('ETF1', 3, 0.06), ('STOCK1', 1, 0.125), ('STOCK1', 2, 0.095), ('STOCK1', 3, 0.113)-- Compute compounded return for each instrumentSELECT InstrumentId, CompoundedReturn = PRODUCT(1 + RateOfReturn) - 1, CompoundedReturnPercentage = FORMAT((PRODUCT(1 + RateOfReturn) - 1) * 100, 'N1') || '%'FROM InstrumentGROUP BY InstrumentId
Result:
| InstrumentId | CompoundedReturn | CompoundedReturnPercentage |
|---|---|---|
| BOND1 | 0.098026 | 9.8% |
| ETF1 | 0.093284 | 9.3% |
| STOCK1 | 0.371077 | 37.1% |
The above query calculates the compounded return for each instrument by taking the product of (1 + RateOfReturn) for all periods and then subtracting 1 to return the CompoundedReturn column. The CompoundedReturnPercentage column shows the same value formatted for display as a percentage with one decimal place.
Using BOND1 as an example, the calculation would be:
| (1 + 0.035) = 1.035 | * | Period 1 return |
| (1 + 0.0275) = 1.0275 | * | Period 2 return |
| (1 + 0.0325) = 1.0325 | = | Period 3 return |
| 1.098026 | – 1 = | Growth factor (includes the original principal $1) |
| 0.098026 | = | Compounded return (i.e., the percentage gain) |
| 9.8% | Isolated profit/loss percentage |
Alternative using OVER (PARTITION BY ...)
Like the first example, this version uses windowing with OVER to calculate the compounded return for each individual row.
SELECT InstrumentId, Period, RateOfReturn, CompoundedReturn = PRODUCT(1 + RateOfReturn) OVER (PARTITION BY InstrumentId) - 1, CompoundedReturnPercentage = FORMAT((PRODUCT(1 + RateOfReturn) OVER (PARTITION BY InstrumentId) - 1) * 100,'N1') || '%'FROM InstrumentORDER BY InstrumentId, Period
Result:
| InstrumentId | Period | RateOfReturn | CompoundedReturn | CompoundedReturnPercentage |
|---|---|---|---|---|
| BOND1 | 1 | 0.0350 | 0.098026 | 9.8% |
| BOND1 | 2 | 0.0275 | 0.098026 | 9.8% |
| BOND1 | 3 | 0.0325 | 0.098026 | 9.8% |
| ETF1 | 1 | 0.0800 | 0.093284 | 9.3% |
| ETF1 | 2 | -0.0450 | 0.093284 | 9.3% |
| ETF1 | 3 | 0.0600 | 0.093284 | 9.3% |
| STOCK1 | 1 | 0.1250 | 0.371077 | 37.1% |
| STOCK1 | 2 | 0.0950 | 0.371077 | 37.1% |
| STOCK1 | 3 | 0.1130 | 0.371077 | 37.1% |
Summary
The new PRODUCT function in SQL Server 2025 brings native multiplicative aggregation to T-SQL, eliminating the need for workarounds when calculating the product of a set of numeric values. It supports standard aggregation with GROUP BY, including DISTINCT, as well as analytic calculations using OVER (PARTITION BY ...) to preserve individual detail rows. As we demonstrated with product prices and compounded investment returns, PRODUCT makes calculations that depend on multiplying values across a set simpler and more expressive.
Happy coding!
