CODEWITHSUNDEEP
sql-servert-sqlaggregateaggregate-functionsweighted-average
user3115933
user3115933

Reputation: 4443

Why is this T-SQL query throwing an "aggregate function" error message?

I am using SQL Server 2014 and I have the following SQL query which runs fine and gives the following output:

SELECT 
    [PropertyCode], [BkgLeadTime], COUNT([Bkg Lead Time]) AS 'BLT COUNT' 
FROM 
    BOOKINGLEADTIME_CTE
GROUP BY 
    [PropertyCode], [Bkg Lead Time]

Output:

PropertyCode     BkgLeadTime        BLT COUNT
----------------------------------------------
   ZIL               1                4
   ZIL               2                2
   ZIL               5                1
   ZIL               7                12

I need to change the above query so that I get only one row as a result. Basically, I want to compute the weighted average of the output shown above.

My new query stands as follows:

SELECT 
    [PropertyCode], 
    SUM([Bkg Lead Time] * COUNT([Bkg Lead  Time])) / SUM (COUNT([Bkg Lead Time])) AS 'Weighted AVG BLT'
FROM 
    BOOKINGLEADTIME_CTE
GROUP BY 
    [PropertyCode]

However, SSMS is throwing the following error when I run the query:

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

How do I modify my query to avoid this error? I understand it has to do with the aggregation syntax I've used but I can't find out the correct way to re-write this query.

Expected output is:

PropertyCode    Weighted AVG BLT
   ZIL             5.10

Upvotes: 2

Views: 108

Answers (2)

StackUser
StackUser

Reputation: 5398

Try like this,

SELECT [PropertyCode]
    ,(SUM([Bkg Lead Time]) * COUNT([Bkg Lead  Time])) / COUNT([Bkg Lead Time]) AS 'Weighted AVG BLT'
FROM BOOKINGLEADTIME_CTE
GROUP BY [PropertyCode]

Upvotes: 1

Abdul Rasheed
Abdul Rasheed

Reputation: 6729

May be this will help you

WITH CTE_TEST
AS
(   SELECT [PropertyCode], [BkgLeadTime], COUNT([Bkg Lead Time]) AS 'BLT_COUNT' 
    FROM BOOKINGLEADTIME_CTE
    GROUP BY [PropertyCode], [Bkg Lead Time]
)
SELECT SUM([BkgLeadTime]*[BLT_COUNT]) / SUM([BLT_COUNT]) FROM CTE_TEST

Upvotes: 3

Related Questions