 # SQL Server SUMPRODUCT function

SUMPRODUCT
Updated: 25 Apr 2017
Use the aggregate function SUMPRODUCT to perform elementwise multiplication on the supplied matrices and return the sum of those products.
Syntax
SELECT [wct].[SUMPRODUCT] (
,<@array, nvarchar(max),>)
Arguments
 Input Name Description @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
• Available in XLeratorDB / math 2008 only
Examples
Example #1

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

 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.

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.

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).  Copyright 2008-2023 Westclintech LLC         Privacy Policy        Terms of Service