PRODUCT (Transact-SQL)

2025-05-19

Applies to: Applies to: SQL Server 2025 (17.x) Preview Azure SQL Database Azure Synapse Analytics SQL database in Microsoft Fabric

Returns the PRODUCT of all the values, or only the DISTINCT values, in the expression. Use with numeric columns only. Null values are ignored.

Transact-SQL syntax conventions

Syntax

Aggregate function syntax.

PRODUCT ( [ ALL | DISTINCT ] expression )

Analytic function syntax.

PRODUCT ( [ ALL ] expression) OVER ( [ PARTITION BY clause ] ORDER BY clause)

Arguments

ALL

Applies the aggregate function to all values. ALL is the default.

DISTINCT

Specifies that PRODUCT returns the PRODUCT of unique values.

expression

A constant, column, or function, and any combination of arithmetic, bitwise, and string operators. expression is an expression of the exact numeric or approximate numeric data type category, except for the bit data type. Aggregate functions and subqueries aren't permitted. For more information, see Expressions.

OVER ( [ PARTITION BY clause ] ORDER BY clause )

Determines the partitioning and ordering of a rowset before the function is applied.

PARTITION BY clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group.

ORDER BY clause determines the logical order in which the operation is performed. Required. For more information, see OVER clause.

Return types

Returns the product of all expression values in the most precise expression data type.

Expression result	Return type
tinyint	int
smallint	int
int	int
bigint	bigint
decimal category (p, s)	If (s == 0): decimal(38, 0) Else: decimal(38, 6)
money and smallmoney category	money
float and real category	float

Remarks

PRODUCT is a deterministic function when used without the OVER and ORDER BY clauses. It's nondeterministic when specified with the OVER and ORDER BY clauses. For more information, see Deterministic and nondeterministic functions.

Examples

The code samples in this article use the AdventureWorks2022 or AdventureWorksDW2022 sample database, which you can download from the Microsoft SQL Server Samples and Community Projects home page.

A. Multiply rows together

The following examples show using the PRODUCT function

SELECT PRODUCT(UnitPrice) AS ProductOfPrices
FROM Purchasing.PurchaseOrderDetail
WHERE ModifiedDate <= '2002-05-24'
GROUP BY ProductId;

Here's a partial result set.

ProductOfPrices
----------
2526.2435
41.916
3251.9077
21656.2655
40703.3993
4785336.3939
11432159532.8367
5898056095.7678

B. Use the OVER clause

The following example uses the PRODUCT function with the OVER clause to provide a rate of return on hypothetical financial instruments. The data is partitioned by finInstrument.

SELECT finInstrument,
    PRODUCT(1 + rateOfReturn)
        OVER (PARTITION BY finInstrument) AS CompoundedReturn
FROM (
    VALUES (0.1626, 'instrumentA'),
           (0.0483, 'instrumentB'),
           (0.2689, 'instrumentC'),
           (-0.1944, 'instrumentA'),
           (0.2423, 'instrumentA'))
AS MyTable(rateOfReturn, finInstrument);

Here's the result set.

finInstrument CompoundedReturn
------------- ---------------------------------------
instrumentA   1.163527
instrumentA   1.163527
instrumentA   1.163527
instrumentB   1.048300
instrumentC   1.268900

Share via