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 (http://blog.SQLAuthority.com) , BOL.

About these ads

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

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

  2. 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

  3. 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

  4. 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!

    • 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

  5. 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.

  6. 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.

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

  8. 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.

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

  10. 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!.

  11. 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

  12. 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.”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s