SQL Server GCD Function

Updated 2024-02-13 19:25:24.957000

Syntax

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

Description

Use the scalar function 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*.*

Arguments

Return Type

float

Remarks

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

Examples

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;

This produces the following result.

{"columns":[{"field":"recno","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"GCD","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"recno":"1","GCD":"7"},{"recno":"2","GCD":"13"},{"recno":"3","GCD":"5"}]}