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
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:
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).
See Also