PRODUCT (Transact-SQL)

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

The PRODUCT function returns the product of all the values, or only the DISTINCT values, in an 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. For more information, see SELECT - 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 is `0`, decimal(38, 0), otherwise decimal(38, 6)
money and smallmoney category	money
float and real category	float

Remarks

Support for PRODUCT in Azure SQL Managed Instance is limited to Azure SQL Managed Instance^AUTD.

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 AdventureWorks2025 or AdventureWorksDW2025 sample database, which you can download from the Microsoft SQL Server Samples and Community Projects home page.

A. Multiply rows together

The following example uses the PRODUCT function:

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

Here's the result set.

ProductOfPrices
----------
2526.2435
41.916
3251.9077
640559.8491
1469352.0378
222137708.073
11432159376.271
5898056028.2633
14030141.2883
2526.4194

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);