SUMPRODUCT

Updated 2023-10-17 14:54:32.583000

Syntax

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

Description

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

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:

{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"column 2","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"column 3","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"column 4","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"1","column 2":"1","column 3":"1","column 4":"1"},{"column 1":"1","column 2":"2","column 3":"4","column 4":"8"},{"column 1":"1","column 2":"3","column 3":"9","column 4":"27"},{"column 1":"1","column 2":"4","column 3":"16","column 4":"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.

{"columns":[{"field":"SUMPRODUCT","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"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.

{"columns":[{"field":"SUMPRODUCT","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"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).

{"columns":[{"field":"SUMPRODUCT","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"SUMPRODUCT":"-1.71585608375343E+54"}]}