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.
pinaldave
Invalid use of ‘PRINT’ within a function.
Chintan,
I do not see relation to your comment with post so I will delete it soon.
Regards,
Pinal
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!
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.