CODEWITHSUNDEEP
sqlsql-server
timberwolf
timberwolf

Reputation: 47

SUMPRODUCT In SQL

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?

Upvotes: 3

Views: 6288

Answers (3)

Edper
Edper

Reputation: 9322

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

Upvotes: 0

Tanner
Tanner

Reputation: 22753

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.

Upvotes: 0

daZza
daZza

Reputation: 1689

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.

Upvotes: 1

Related Questions