Login     Register

        Contact Us     Search

XLeratorDB/math Documentation

SQL Server GCD function


GCD
 
Updated: 17 August 2010 
Use GCD to calculate the greatest common divisor of all the values, or only the DISTINCT values, in the expression. GCD can be used with numeric columns only. Null values are ignored. May be followed by the OVER clause.
Syntax
SELECT wct.GCD ( [ ALL | DISTINCT ] expression )
Arguments
ALL
Applies the aggregate function to all values. ALL is the default.
 
DISTINCT
Specifies that GCD 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 greatest common divisor 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
 
If you haven’t already done so, create the following table.
CREATE TABLE [dbo].[g1](
      [recno] [float] NOT NULL,
      [seqno] [float] NOT NULL,
      [num_test] [float] NOT NULL,
 CONSTRAINT [PK_g1] PRIMARY KEY CLUSTERED
(
      [recno] ASC,
      [seqno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
If you haven’t already done so, insert the following values into the table.
 
INSERT INTO g1 VALUES (1,1,7)
INSERT INTO g1 VALUES (1,2,21)
INSERT INTO g1 VALUES (1,3,35)
INSERT INTO g1 VALUES (1,4,49)
INSERT INTO g1 VALUES (1,5,77)
INSERT INTO g1 VALUES (2,1,13)
INSERT INTO g1 VALUES (2,2,78)
INSERT INTO g1 VALUES (2,3,26)
INSERT INTO g1 VALUES (2,4,169)
INSERT INTO g1 VALUES (3,1,5)
INSERT INTO g1 VALUES (3,2,25)
INSERT INTO g1 VALUES (3,3,625)
INSERT INTO g1 VALUES (3,4,390625)
Run the following select statement.
 
select recno,
wct.GCD(num_test) as GCD
from g1
where recno < 4
group by recno
recno                  GCD
---------------------- ----------------------
1                      7
2                      13
3                      5
 
(3 row(s) affected)


Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service