Clever Geek Handbook

SUMPRODUCT IN SQL

Hi, I am trying to replicate the sumproduct I am using with SQL but am afraid. I have some values ​​for some asset types and another table with weights.

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 generate 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 use SUMPRODUCT to apply different weight categories to simulations

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

+3


source to share


3 answers


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'


Cm.

0


source


This should be the main implementation of the sumproduct equivalent:

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


Try to customize this for your case.

+1


source


I was able to create a working solution in SQL Server, albeit perhaps with some optimizations to shrink 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)

Product 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 computation to the SUM

bottom Values * Percentages

:

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

Issues

| 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 from yours, but I'm working on the fact that you have an error in your total product formula. Where do 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 assumed 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, replace C3:E3

with B3:D3

in the second column, as it refers otherwise to an empty cell.

0


source






More articles:


All Articles