Search code examples
sqlsql-servert-sqlgroup-by

Perform an aggregate function on an expression containing an aggregate

I have the following table in SQL server database.

FundID FundType Allocation Performance
1 Credit 0.10 0.15
2 Credit 0.20 0.25
3 Credit 0.30 0.35
4 Credit 0.40 0.45
5 Credit 0.50 0.55
6 Equity 0.60 0.65
7 Equity 0.70 0.75
8 Equity 0.80 0.85
9 Cash 0.90 0.95
10 Cash 0.99 1.55

I would like to derive Weighted Performance for each Fund Type based on the following formula:

WeightedPerformance =  SUM ( (Allocation/SUM(Allocation Per FundType) * Performance)

Example for Credit:

Sum of Allocation =  1.50 (0.10+0.20+0.30+0.40+0.50)

WeightedPerformance = Sum(((0.1/1.5)*0.15) + ((0.2/1.5)*0.25) + ((0.3/1.5)*0.35) + ((0.4/1.5)*0.45)+ ((0.5/1.5)*0.55) )

WeightedPerformance of Credit = 0.416666666666667

How can I calculate this using T-SQL for each FundTypes?

Query so far:

SELECT 
        FundType, 
        Sum((Allocation/Sum(Allocation))*Performance) as WeightedPerformance 
FROM
       FundAllocation
GROUP BY
       FundType

But getting:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Note: this question is not duplicate of SQL Server "cannot perform an aggregate function on an expression containing an aggregate or a subquery", but Sybase can because the suggested question is using grouped by clause for further aggregation. In this case, it is grouped by FundType.

Solution

  • I presume that

       Sum((Allocation/Sum(Allocation))*Performance)
is equal to
   Sum(Allocation*Performance)/sum(Allocation)

    There you do not use aggregation expression in a aggregation function.

    See example

    -- simplest solution
SELECT 
   FundType, 
   Sum(Allocation*Performance)/sum(Allocation) as WeightedPerformance 
  ,Sum(Allocation) Sum_Allocation
FROM FundAllocation 
GROUP BY FundType
    FundType WeightedPerformance Sum_Allocation
    Cash 1.26428571428571 1.89
    Credit 0.416666666666667 1.5
    Equity 0.75952380952381 2.1

    Second example - use subquery to calculate Sum(Allocation)over(partition by FundType)

    -- with subquery
SELECT 
   FundType, 
   Sum((Allocation/Sum_Allocation)*Performance) as WeightedPerformance 
  ,min(Sum_Allocation) Sum_Allocation
FROM(
  select *, Sum(Allocation)over(partition by FundType) as Sum_Allocation
  from FundAllocation
  )a
GROUP BY FundType
    FundType WeightedPerformance Sum_Allocation
    Cash 1.26428571428571 1.89
    Credit 0.416666666666667 1.5
    Equity 0.75952380952381 2.1

    fiddle