CODEWITHSUNDEEP
sqlsql-serveraggregate-functions
user3115933
user3115933

Reputation: 4443

How to re-write my T-SQL query to eliminate the "aggregate function on an expression" error?

I am using SQL Server 2014 and I have the following query that is giving me a specific error message: "Cannot perform an aggregate function on an expression containing an aggregate or a subquery".

 USE MyDatabase
 SELECT [MTH], 
        [RoomTypeCode], 
        SUM ([RN] * [AdultCount] / SUM ([RN])) AS    'Weighted Adult Ratio',
        SUM ([RN] * [ChildCount] / SUM ([RN])) AS 'Weighted Child Ratio'


 FROM PaxRatio

 WHERE [PropertyCode] = 'XYZ'

 AND [MTH] between '2015-07-01' and '2016-09-30'

The objective of the query is to give me the weighted averages of the AdultCount and ChildCount columns in my View Table (called PaxRatio).

I've tried with a CTE table rather than a VIEW Table but I am still getting the same error!

Upvotes: 0

Views: 65

Answers (2)

user4622594
user4622594

Reputation:

I think you only have to change the position of the brackets:

 SELECT [MTH], 
        [RoomTypeCode], 
        SUM ([RN] * [AdultCount]) / SUM ([RN]) AS 'Weighted Adult Ratio',
        SUM ([RN] * [ChildCount]) / SUM ([RN]) AS 'Weighted Child Ratio'
 FROM PaxRatio
 WHERE [PropertyCode] = 'XYZ'
 AND [MTH] between '2015-07-01' and '2016-09-30'

Upvotes: 2

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You cannot perform a SUM within another SUM function. You can instead calculate the SUM in a separate query and use CROSS JOIN in order to consume the computed value in your main query:

 SELECT [MTH], 
        [RoomTypeCode], 
        SUM ([RN] * [AdultCount] / sum_rn) AS 'Weighted Adult Ratio',
        SUM ([RN] * [ChildCount] / sum_rn) AS 'Weighted Child Ratio'
 FROM PaxRatio
 CROSS JOIN (
    SELECT SUM([RN]) AS sum_rn
    FROM PaxRatio
    WHERE [PropertyCode] = 'XYZ' AND 
          [MTH] BETWEEN '2015-07-01' AND '2016-09-30') t
 WHERE [PropertyCode] = 'XYZ' AND 
       [MTH] between '2015-07-01' and '2016-09-30'

Upvotes: 0

Related Questions