Login     Register

        Contact Us     Search

XLeratorDB/math Documentation

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 NameDescription
@arrayThe 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:

1111
1248
13927
141664
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


Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service