SQL SERVER – Fix: Error 130: Cannot perform an aggregate function on an expression containing an aggregate or a subquery

Fix: Error 130: Cannot perform an aggregate function on an expression containing an aggregate or a subquery

Following statement will give the following error: “Cannot perform an aggregate function on an expression containing an aggregate or a subquery.” MS SQL Server doesn’t support it.
USE PUBS
GO
SELECT AVG(COUNT(royalty)) RoyaltyAvg
FROM dbo.roysched
GO

You can get around this problem by breaking out the computation of the average in derived tables.
USE PUBS
GO
SELECT AVG(t.RoyaltyCounts)
FROM
(
SELECT COUNT(royalty) AS RoyaltyCounts
FROM dbo.roysched
) T
GO

Reference : Pinal Dave (https://blog.sqlauthority.com) , BOL.

Solarwinds
, , ,
Previous Post
SQL SERVER – Binary Sequence Generator – Truth Table Generator
Next Post
SQL SERVER – Fix : Error 1205 : Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction

Related Posts

30 Comments. Leave new

  • Thomas Sebastian
    July 12, 2012 8:08 am

    Awesome! Perfect, you helped me out a lot man! Made me learn and scratch my head, but it allowed a much easier method than the standard “group by”, especially if multiple tables/fields are involved!.

    Reply
  • all i want is to subtract the count record from one table into another.. but the below query is showing the total subtraction of count value, not grouped by. so please help me…

    Select COUNT(*)
    FROM
    (SELECT OfficeRegId
    FROM [LabourDept].[dbo].[PanjikaranMaster]
    WHERE NOT EXISTS
    (SELECT OfficeRegId FROM [LabourDept].[dbo].[AbhidayMaster]
    WHERE [LabourDept].[dbo].[PanjikaranMaster].OfficeRegId=[LabourDept].[dbo].[AbhidayMaster].OfficeRegId
    AND [LabourDept].[dbo].[AbhidayMaster].DipositDate Between (‘2012-09-19’) AND (‘2012-09-19’))
    ) as ANPS

    Reply
  • Mohammed Kaleem
    January 10, 2013 10:24 am

    Thanks a lot … Its working fine ……

    Reply
  • Gaurab Chatterjee
    July 30, 2013 5:10 pm

    I try your above code in function but unable to compile it . It throws me error
    “Select statements included within a function cannot return data to a client.”

    Reply
  • Thank you Pinal! Saved me some headache :D

    Reply
  • Thank you Pinal! Saved me some headache!

    Reply
  • Aggregating Correlated Sub-Queries: Resolving this situation can sometimes be tricky, but it can always be done.

    Reply

Leave a Reply

Menu