MySQL: calculate weighted holdings of an ETF portfolio with the result to get one list with all holdings accumulated

Question

I have a MySQL table with around 900 entries of different ETF holdings. Here is an example:

ID    ETF Name                                       Stock Name           Weighting     ISIN
1     iShares Automation & Robotics                  XIAOMI CORP          3.45          KYG9830T1067
24    iShares Automation & Robotics                  SNAP INC CLASS A     3.37          US83304A1060       
42    iShares Automation & Robotics                  APPLE INC            2.14          US0378331005       
51    iShares MSCI World Information Technology      APPLE INC            20.14         US0378331005
53    iShares MSCI World Information Technology      MICROSOFT CORP       14.04         US5949181045

My goal is now to set ETF weightings, for example for the ETF iShares Automation & Robotics 20% and for the ETF iShares MSCI World Information Technology 80%. What the SQL query should now do is to calculate the weighted holding in dependence of the 20/80 ETF diversification.

For example for the stock named Apple the result would be: 0.2 * 2.14 + 0.8 * 20.14 = 16.54. At the end I would like to have one table which removes all duplicates (by ISIN) and gives me one table with the calculated weights ordered by weightings.

Here is the expected output (for 20/80):

Stock Name            Weighting     ISIN
APPLE INC             16.54         US0378331005
MICROSOFT CORP        11.232        US5949181045
XIAOMI CORP           0.69          KYG9830T1067
SNAP INC CLASS A      0.674         US83304A1060

Sadly I don't even have a clue where to start... You can match the stocks by using the ISIN which stands for International Securities Identification Number and is an unique stock id!

I would appreciate any kind of help!

Answer 1

You can do it with conditional aggregation:

SELECT `Stock Name`,
       ROUND(SUM(CASE 
             WHEN `ETF Name` = 'iShares Automation & Robotics' THEN 0.2 
             WHEN `ETF Name` = 'iShares MSCI World Information Technology' THEN 0.8
           END * Weighting
       ), 3) Weighting,     
       ISIN 
FROM tablename
GROUP BY `Stock Name`, ISIN

See the demo.
Results:

> Stock Name       | Weighting | ISIN        
> :--------------- | --------: | :-----------
> APPLE INC        |    16.540 | US0378331005
> MICROSOFT CORP   |    11.232 | US5949181045
> SNAP INC CLASS A |     0.674 | US83304A1060
> XIAOMI CORP      |     0.690 | KYG9830T1067