SQL Server SUMPRODUCT Function

Updated 2023-10-17 14:54:32.583000

Description

Use the aggregate function SUMPRODUCT to perform elementwise multiplication on the supplied matrices and return the sum of those products.

Syntax

SELECT [westclintech].[wct].[SUMPRODUCT] (
  ,<@array, nvarchar(max),>)

Arguments

@array

The string representation of a matrix with columns separated by commas and rows separated by semi-colons.

Return Type

float

Remarks

Each @array must have the same number of rows and columns or NULL is returned.

Each element of @array must be numeric.

Embedded formula (e.g. 1/4 instead of 0.25) will generate a NULL return value

Examples

Example #1

In this example we calculate the SUMPRODUCT for a single matrix: for a single matrix:

column 1	column 2	column 3	column 4
1	1	1	1
1	2	4	8
1	3	9	27
1	4	16	64

DECLARE @a as nvarchar(max)
    = wct.MATRIX2STRING_q('
    SELECT
        POWER(n.x,m.p1),
        POWER(n.x,m.p2),
        POWER(n.x,m.p3),
        POWER(n.x,m.p4)
    FROM (VALUES (1),(2),(3),(4))n(x)
    CROSS APPLY (VALUES (0,1,2,3))m(p1,p2,p3,p4)');
SELECT wct.SUMPRODUCT(@a) as SUMPRODUCT;

This produces the following result.

SUMPRODUCT
144

Example #2

In this example, we calculate the reciprocal for every element in @a from the previous example and then calculate the SUMPRODUCT of the 2 matrices.

DECLARE @b as nvarchar(max) = wct.MUPDATE(1, NULL, NULL, NULL, NULL, '/', @a, NULL,
          NULL, NULL, NULL);
SELECT wct.SUMPRODUCT(x) as SUMPRODUCT
FROM
(
    VALUES
        (@a),
        (@b)
) n (x);

This produces the following result.

SUMPRODUCT
16

Example #3

In this example we randomly generate the values for 3 matrices and calculate the SUMPRODUCT .

SELECT
     Seq
    ,SeriesValue
    ,FLOOR((Seq-1)/150) + 1 as MatrixNo
    ,((Seq-1) % 50) + 1 as RowNo
    ,((Seq-1) % 30) + 1 as ColNo
INTO
    #m
FROM
    wctMath.wct.SeriesInt(-100,100,NULL,4500,'R');
   
SELECT MatrixNo, wctMath.wct.NMATRIX2STRING(Rowno,ColNo,SeriesValue) as matrix
INTO #s
FROM #m
GROUP BY MatrixNo;   
   
SELECT wct.SUMPRODUCT(MATRIX) as SUMPRODUCT
FROM #s;

This produces the following result (your result will be different).

SUMPRODUCT
-1.71585608375343E+54