CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-by
developer
developer

Reputation: 1687

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.

Upvotes: 1

Views: 80

Answers (3)

ValNik
ValNik

Reputation: 5916

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

Upvotes: 3

Michał Turczyn
Michał Turczyn

Reputation: 37460

You need to use a subquery or Common Table Expression (CTE) to first calculate the sum of allocation per FundType before performing the weighted calculation. Here’s how you can achieve this:

T-SQL Query:

WITH FundAllocationSums AS (
    SELECT 
        FundType, 
        SUM(Allocation) AS TotalAllocation
    FROM FundAllocation
    GROUP BY FundType
)
SELECT 
    f.FundType,
    SUM((f.Allocation / fas.TotalAllocation) * f.Performance) AS WeightedPerformance
FROM FundAllocation f
JOIN FundAllocationSums fas 
    ON f.FundType = fas.FundType
GROUP BY f.FundType;

Table & Sample Data for SQL Fiddle:

Use this to set up the table in SQL Server:

CREATE TABLE FundAllocation (
    FundID INT PRIMARY KEY,
    FundType VARCHAR(50),
    Allocation DECIMAL(10, 5),
    Performance DECIMAL(10, 5)
);

INSERT INTO FundAllocation (FundID, FundType, Allocation, Performance) VALUES
(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);

Upvotes: 1

Charlieface
Charlieface

Reputation: 72197

You can use a window function for this

WITH summed AS (
    SELECT 
       fa.*,
       SUM(fa.Allocation) OVER (PARTITION BY fa.FundType) AS AllocationPerFundType
    FROM FundAllocation fa
)
SELECT
  fa.FundType,
  SUM(fa.Allocation / fa.AllocationPerFundType * fa.Performance) as WeightedPerformance,
  SUM(Allocation) AS SumAllocation
FROM summed fa
GROUP BY
  fa.FundType;

db<>fiddle

Upvotes: 1

Related Questions