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.

, , ,
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

31 Comments. Leave new

  • Chintan,
    I do not see relation to your comment with post so I will delete it soon.
    Regards,
    Pinal

    Reply
  • Invalid use of ‘PRINT’ within a function.

    Reply
  • I don’t understand how you can reference t.royaltycounts in your solution. Is that what the capital T is for outside the parenthesis?

    Reply
  • Hi Pinal Dave,
    Thank you for the query. It worked appropriately for me.

    Regards
    Mahathi.

    Reply
  • Hi,
    First time when I used this, it worked correctly. But now what happens is that, if the total count of all the records is 16 and if there are four items on which we apply count, it is dividing those 16 records between the four items and displaying the result.

    Please help me in that error.

    Regards
    Mahathi

    Reply
  • The query I used for it is

    ALTER procedure [dbo].[mostFrequentAllocations](@ClientId bigint)
    as
    begin
    select a.AllocName, max(av.AllocationId) as Cnt from (select count(AllocId) as AllocationId from AllocationVisit)av, Allocations a,Clients C where A.ClientId=C.ClientId and c.ClientId=@ClientId
    group by a.AllocName

    end

    Reply
  • i have a code similar to the code you gave, i just changed the fields..

    SELECT SUM(t.Total Applications)
    FROM
    (
    SELECT dbo.aBarangay.BarangayName, (COUNT(BPAS.Permits.PermitID)) AS ‘Total Applications’
    FROM BPAS.Permits LEFT OUTER JOIN
    BPAS.BuildingRecord ON BPAS.Permits.BldgID = BPAS.BuildingRecord.BldgID AND
    BPAS.Permits.BldgID = BPAS.BuildingRecord.BldgID RIGHT OUTER JOIN
    dbo.aBarangay ON BPAS.BuildingRecord.BrgyID = dbo.aBarangay.BarangayId
    GROUP BY dbo.aBarangay.BarangayName
    ORDER BY dbo.aBarangay.BarangayName
    ) T

    it gives me this error:

    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ‘Applications’.
    Msg 156, Level 15, State 1, Line 10
    Incorrect syntax near the keyword ‘ORDER’.

    what should i do?

    thanks!

    Reply
    • SELECT SUM(t.Total Applications),BarangayName
      FROM
      (
      SELECT dbo.aBarangay.BarangayName, (COUNT(BPAS.Permits.PermitID)) AS ‘Total Applications’
      FROM BPAS.Permits LEFT OUTER JOIN
      BPAS.BuildingRecord ON BPAS.Permits.BldgID = BPAS.BuildingRecord.BldgID AND
      BPAS.Permits.BldgID = BPAS.BuildingRecord.BldgID RIGHT OUTER JOIN
      dbo.aBarangay ON BPAS.BuildingRecord.BrgyID = dbo.aBarangay.BarangayId

      ) T
      GROUP BY dbo.aBarangay.BarangayName
      ORDER BY dbo.aBarangay.BarangayName

      Reply
  • hi dave,

    i need to use Max() like this,

    select id, Max( (select y from z where t =’a’)) from c
    group by e,f
    when I use the subquery, I was getting same above error…

    please help.

    Reply
  • Thanks, that worked appropriately for me.

    Reply
  • great Blog Pinal keep it up…
    Ur suggessions are dam helpful
    thnx…

    -DEV

    Reply
  • Dave,

    Regarding your solution, suppose I have to take average of the count of rows, grouped by hour.

    So I have the following data:

    row 1: 02/01/10 05:00:00
    row 2: 02/01/10 07:10:00
    row 3 02/01/10 12:00:00
    row 4: 02/02/10 05:00:00
    row 5: 02/02/10 05:23:00
    row 6: 02/02/10 05:50:00
    row 7: 02/02/10 07:32:00

    and I want the results to be average of the row count, hour:
    avg_row_count hr
    =========== ==
    2 05
    1 07
    0.5 12

    I cannot write the sql because of the aggragation being done on hour.

    Reply
  • Hi Pinal Dave,

    Grt work Buddy… Keep it up… God Bless.

    Thanks !

    Reply
  • when i used the below expression in the sub query, i am getting the error

    SUM((CASE
    WHEN ON_DATE <= CalcOnDate THEN
    1
    ELSE
    0
    END)/COUNT(SL.PACKLIST_ID)) AS [OnTime%]..

    since i have another column like COUNT(SL.PACKLIST_ID)– i have converted the column as

    (SELECT T.SL_PACKLIST_ID FROM ( SELECT COUNT(PACKLIST_ID) AS SL_PACKLIST_ID FROM SHIPPER_LINE) AS T) AS TopShippedLines

    It worked fine for me but when i used in the above expression it is giving the error :Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

    what could be the problem?
    can help to resolve this issue

    Reply
  • Thank a lot it works fine for me

    Reply
  • anantharengan
    May 2, 2012 2:53 pm

    thanks a lot.

    Reply
  • Thanks buddy

    Reply
  • Vikram Singh Saini
    May 16, 2012 1:20 am

    I’m not so much expert in Sql. I found a single rectification in code sample provided(missing of AS keyword before T) as I faced problem due to that.

    USE PUBS
    GO
    SELECT AVG(t.RoyaltyCounts)
    FROM
    (
    SELECT COUNT(royalty) AS RoyaltyCounts
    FROM dbo.roysched
    ) AS T
    GO

    Thanks Pinal Dave for same.

    Reply
  • Vikram Singh Saini
    May 16, 2012 1:22 am

    Sorry for the comment! It is working fine. The problem was in some other part of code. Please accept my apology.

    Reply
  • thanks man, i just did a little modification and it turns exactly the way i want it to be.

    Reply
  • saianirudh kantabathina
    July 10, 2012 3:38 am

    Thanks you saved my day

    Reply

Leave a Reply