PRODUCT

Updated 2024-02-13 19:40:00.757000

Syntax

SELECT westclintech.wct.PRODUCT ( [ ALL | DISTINCT ] expression )

Description

Use the aggregate function PRODUCT to calculate the product of all the values, or only the DISTINCT values, in the expression. PRODUCT can be used with numeric columns only. Null values are ignored. May be followed by the OVER clause*.*

Arguments

@expression

is 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 are not permitted.

Return Type

float

Remarks

Distinct aggregates are not supported when CUBE or ROLLUP are used. If used, the SQL Server Engine returns an error message and cancels the query.

Examples

CREATE TABLE [dbo].[int_fact]
(
    [mth] [int] NOT NULL,
    [int_fact_desc] [nvarchar](50) NOT NULL,
    [int_fact] [float] NOT NULL,
    Primary Key (
                    [mth] ASC,
                    int_fact_desc ASC
                )
);
INSERT INTO int_fact
VALUES
(0, 'LIBOR1M', 0.015);
INSERT INTO int_fact
VALUES
(1, 'LIBOR1M', 0.0175);
INSERT INTO int_fact
VALUES
(2, 'LIBOR1M', 0.0195);
INSERT INTO int_fact
VALUES
(3, 'LIBOR1M', 0.02175);
INSERT INTO int_fact
VALUES
(4, 'LIBOR1M', 0.02225);
INSERT INTO int_fact
VALUES
(5, 'LIBOR1M', 0.0223745);
INSERT INTO int_fact
VALUES
(6, 'LIBOR1M', 0.025);
INSERT INTO int_fact
VALUES
(7, 'LIBOR1M', 0.02625);
INSERT INTO int_fact
VALUES
(8, 'LIBOR1M', 0.02325);
INSERT INTO int_fact
VALUES
(9, 'LIBOR1M', 0.021215);
INSERT INTO int_fact
VALUES
(10, 'LIBOR1M', 0.02);
INSERT INTO int_fact
VALUES
(11, 'LIBOR1M', 0.0195);
INSERT INTO int_fact
VALUES
(0, 'LIBOR3M', 0.02);
INSERT INTO int_fact
VALUES
(3, 'LIBOR3M', 0.0225);
INSERT INTO int_fact
VALUES
(6, 'LIBOR3M', 0.024);
INSERT INTO int_fact
VALUES
(9, 'LIBOR3M', 0.02625);

To calculate the value of two investment alternatives, one year hence, given the interest factors, enter the following SELECT statement:

Select int_fact_desc,
       CASE
           WHEN int_fact_desc = 'LIBOR1M' then
               wct.PRODUCT(1 + int_fact / 12)
           WHEN int_fact_desc = 'LIBOR3M' then
               wct.PRODUCT(1 + int_fact / 4)
       END * 100000 as FV
from int_fact
GROUP BY int_fact_desc;

Here is the result set.

{"columns":[{"field":"int_fact_desc"},{"field":"FV","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"int_fact_desc":"LIBOR1M","FV":"102133.79720865"},{"int_fact_desc":"LIBOR3M","FV":"102338.924934961"}]}