SUMPRODUCT In SQL

Question

Hi I am trying to replicate a sumproduct I use with SQL but am struggling. I have some values for some asset types and another table with weightings.

R1    TBL1          
R2     Sim      Type A  Type B  Type C
R3     1          1.836 1.794   1.153
R4     2          1.629 1.128   1.928
R5     3          1.616 1.956   1.411
R6     4          1.350 1.590   1.958
R7
R8 
R9     TBL2         
R10    Asset ID Type A  Type B  Type C
R11    BA Der       12%     2%      5%
R12    BSL ENH      0%      20%     1%
R13    BSL Der      42%     6%      7%

In Excel I use the following formulas to create my output:

Output (formulas)       
Sim  BA Der                                     BSL ENH
1    =SUMPRODUCT(B3:D3,$B$11:$D$11)  =SUMPRODUCT(B3:D3,$B$12:$D$12)
2    =SUMPRODUCT(B4:D4,$B$11:$D$11)  =SUMPRODUCT(B4:D4,$B$12:$D$12)
3    =SUMPRODUCT(B5:D5,$B$11:$D$11)  =SUMPRODUCT(B5:D5,$B$12:$D$12)
4    =SUMPRODUCT(B6:D6,$B$11:$D$11)  =SUMPRODUCT(B6:D6,$B$12:$D$12)

Output (values)
Sim   BA Der            BSL ENH
1    0.313824843    0.37037487
2    0.314473553    0.244925331
3    0.303555238    0.405301715
4    0.291739471    0.33764572

So essentially I am using SUMPRODUCT to apply different weighting categories to the simulations

I am looking to do this in Access or SQL Server, any suggestions?

What is the desired output please? Also does this help? http://stackoverflow.com/questions/3912204/why-is-there-no-product-aggregate-function-in-sql/3912258#3912258 — gbn, Aug 07 '14 at 08:39
The numbers in your formula don't match up to the tables you provided. Could you label the rows and columns maybe? Or paste an Excel picture? — NickyvV, Aug 07 '14 at 08:50
Are the percent values in tbl2 text or numeric or could they be either; i.e. will they be saved as `12%` or could they simply be saved as `12`. — Tanner, Aug 07 '14 at 08:56
What is the common field between `TBL1` and `TBL2` tables? Because most likely you need to join the two tables. — Edper, Aug 07 '14 at 09:27
Is there an error in you sumproduct formula as for `BA Der` in the first row you reference `B3:D3` and for `BSL ENH` you reference `C3:E3`? Should they both be `B3:D3`? — Tanner, Aug 07 '14 at 09:41

daZza · Answer 1 · 2014-08-07T09:35:12.437

1

This should be the basic implementation of a sumproduct equivalent:

SELECT id, SUM(type * weight) AS sum_product FROM tbl_TableName GROUP BY id

Try adjusting this to your case.

edited Aug 07 '14 at 09:35

answered Aug 07 '14 at 08:43

daZza

1,669
1
29
51

I don't believe dividing by `SUM(type)` is necessary, as it only is a sum of the products. – NickyvV Aug 07 '14 at 08:46
Excels `SUMPRODUCT()` function provides a weighted average iirc. In that case the dividing is necessary. Could be that I am wrong though, I'll check the Excel implementation when I have time – daZza Aug 07 '14 at 08:58
As the formula says, it just: "Multiplies corresponding components in the given arrays, and returns the sum of those products." :) http://office.microsoft.com/en-us/excel-help/sumproduct-function-HP010342935.aspx – NickyvV Aug 07 '14 at 09:30
You are correct, I guess I just always used the SUMPRODUCT function to calculate weighted averages and it stuck to my memory. Edited accordingly. – daZza Aug 07 '14 at 09:35

Tanner · Answer 2 · 2014-08-07T09:59:20.327

I've managed to create a working solution in SQL Server, although it could probably to with some optimisation to reduce the SQL.

SQL Fiddle Demo

Create Script:

CREATE TABLE Tbl1 ([Sim] int, [TypeA] float, [TypeB] float, [TypeC] float)

INSERT INTO Tbl1 ([Sim], [TypeA], [TypeB], [TypeC])
VALUES (1, 1.836, 1.794, 1.153),
       (2, 1.629, 1.128, 1.928),
       (3, 1.616, 1.956, 1.411),
       (4, 1.350, 1.590, 1.958)

CREATE TABLE Tbl2 ([Asset_ID] varchar(7), [TypeA] int, [TypeB] int, [TypeC] int)

INSERT INTO Tbl2 ([Asset_ID], [TypeA], [TypeB], [TypeC])
VALUES ('BA_Der', 12, 2, 5),
      ('BSL_ENH', 0, 20, 1),
      ('BSL_Der', 42, 6, 7)

Sum Product Equivalent

select Sim,
cast(
      (select t1.TypeA*(t2.TypeA*0.01) 
       from tbl2 t2 
       where t2.Asset_ID = 'BA_Der') 
    + (select t1.TypeB*(t2.TypeB*0.01) 
       from tbl2 t2 where t2.Asset_ID = 'BA_Der') 
    + (select t1.TypeC*(t2.TypeC*0.01) 
       from tbl2 t2 
       where t2.Asset_ID = 'BA_Der') as decimal(18,10)) [BA Der],
cast(
      (select t1.TypeA*(t2.TypeA*0.01) 
       from tbl2 t2 
       where t2.Asset_ID = 'BSL_ENH') 
    + (select t1.TypeB*(t2.TypeB*0.01) 
       from tbl2 t2 
       where t2.Asset_ID = 'BSL_ENH') 
    + (select t1.TypeC*(t2.TypeC*0.01) 
       from tbl2 t2 
       where t2.Asset_ID = 'BSL_ENH') as decimal(18,10)) [BSL ENH]
from Tbl1 t1

This essentially breaks down the calculation to SUM the total of the Values * Percentages:

  (TypeA_Value * TypeA_Percentage) 
+ (TypeB_Value * TypeB_Percentage) 
+ (TypeC_Value * TypeC_Percentage) = SUMPRODUCT

Produces

| SIM |  BA DER | BSL ENH |
|-----|---------|---------|
|   1 | 0.31385 | 0.37033 |
|   2 | 0.31444 | 0.24488 |
|   3 | 0.30359 | 0.40531 |
|   4 |  0.2917 | 0.33758 |

This output is different to yours, but I am working on the assumption that you have an error in your sum product formula. Where you have:

Output (formulas)       
Sim  BA Der                                     BSL ENH
1    =SUMPRODUCT(B3:D3,$B$11:$D$11)  =SUMPRODUCT(C3:E3,$B$12:$D$12)
.    ....                            ...

I have assumed that the formulas should be:

Output (formulas)       
Sim  BA Der                                     BSL ENH
1    =SUMPRODUCT(B3:D3,$B$11:$D$11)  =SUMPRODUCT(B3:D3,$B$12:$D$12)
.    ....                            ...

So replaced C3:E3 with B3:D3 in the second column as it's otherwise referencing an empty cell.

score 0 · Accepted Answer · answered Aug 07 '14 at 10:19

Try:

SELECT Sim, ((A.TypeA * B.TypeA) + (A.TypeB * B.TypeB) + (A.TypeC * B.TypeC)) 'BA Der',
((A.TypeA * C.TypeA) + (A.TypeB * C.TypeB) + (A.TypeC * C.TypeC)) 'BSL ENH'
FROM tbl1 A, tbl2 B, tbl2 C
WHERE B.AssetID = 'BA Der'
AND C.AssetID = 'BSL ENH'

See Demo

SUMPRODUCT In SQL

3 Answers3

SQL Fiddle Demo