Skip to content

product-aggregate-transact-sql.md

Latest commit

 

History

History
156 lines (116 loc) · 5.02 KB

product-aggregate-transact-sql.md

File metadata and controls

156 lines (116 loc) · 5.02 KB
title PRODUCT (Transact-SQL)
description PRODUCT (Transact-SQL)
author AaronPitman
ms.author aaronpitman
ms.reviewer randolphwest
ms.date 11/18/2025
ms.service sql
ms.subservice t-sql
ms.topic reference
ms.custom
ignite-2025
f1_keywords
PRODUCT
PRODUCT_TSQL
helpviewer_keywords
values [SQL Server], PRODUCT of all
PRODUCT function [Transact-SQL]
values [SQL Server]
PRODUCT [SQL Server]
expressions [SQL Server], PRODUCT of all values
DISTINCT keyword
totals [SQL Server], PRODUCT
multiply values [SQL Server]
dev_langs
TSQL
monikerRange =fabric || =sql-server-ver17 || =sql-server-linux-ver17

PRODUCT (Transact-SQL)

[!INCLUDE sqlserver2025-asdb-asmi-asa-fabricdw-fabricsqldb]

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.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: 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 [!INCLUDE ssazuremi-md] is limited to [!INCLUDE ssazure-sqlmi-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

[!INCLUDE article-uses-adventureworks]

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;

[!INCLUDE ssresult-md]

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

[!INCLUDE ssresult-md]

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

Related content