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.
31 Comments. Leave new
Invalid use of ‘PRINT’ within a function.
I don’t understand how you can reference t.royaltycounts in your solution. Is that what the capital T is for outside the parenthesis?
Hi Pinal Dave,
Thank you for the query. It worked appropriately for me.
Regards
Mahathi.
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
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
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
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.
Thanks, that worked appropriately for me.
great Blog Pinal keep it up…
Ur suggessions are dam helpful
thnx…
-DEV
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.
Hi Pinal Dave,
Grt work Buddy… Keep it up… God Bless.
Thanks !
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
Post the full query you have used
Thank a lot it works fine for me
thanks a lot.
Thanks buddy
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.
The keyword AS is optional in this case
Sorry for the comment! It is working fine. The problem was in some other part of code. Please accept my apology.
thanks man, i just did a little modification and it turns exactly the way i want it to be.
Thanks you saved my day
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!.