Login     Register

        Contact Us     Search

XLeratorDB/math Documentation

SQL Server PRODUCT function


PRODUCT
 
Updated: 17 August 2010 
Use 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.
Syntax
SELECT wct.PRODUCT ( [ ALL | DISTINCT ] expression )
Arguments
ALL
applies the aggregate function to all values. ALL is the default.
 
DISTINCT
specifies that PRODUCT return the product of unique values.
 
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 Types
Returns the product of all expression values in the most precise expression data type.
Remarks
·         Distinct aggregates are not supported when CUBE or ROLLUP are used. If used, the SQL Server 2005 Database Engine returns an error message and cancels the query.
Example
 
CREATE TABLE [dbo].[int_fact](
      [mth] [int] NOT NULL,
      [int_fact_desc] [nvarchar](50) NOT NULL,
      [int_fact] [float] NOT NULL,
 CONSTRAINT [PK_int_fact] PRIMARY KEY CLUSTERED
(
      [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, eneter 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
int_fact_desc                                      FV
-------------------------------------------------- ----------------------
LIBOR1M                                            102133.79720865
LIBOR3M                                            102338.924934961
 
(2 row(s) affected)


Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service