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
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
Thanks a lot … Its working fine ……
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.”
Thank you Pinal! Saved me some headache :D
Thank you Pinal! Saved me some headache!
Aggregating Correlated Sub-Queries: Resolving this situation can sometimes be tricky, but it can always be done.
Hi, tried that strategy with this query, but still getting the same error.
I tried a group by, same issue. Appreciate any feedback, t hank you.
Select SUM(( Select (SUM([INRT.qty_on_hand] + [INRT.last_count_qty_on_hand])) AS TOTQTY FROM [DEMO10].[dbo].[itemloc_mst] INRT) – (ISNULL(OUTT.last_count_qty_rsvd,0) + ISNULL(OUTT.qty_contained,0) + ISNULL(OUTT.assigned_to_be_picked_qty,0))) FROM [DEMO10].[dbo].[itemloc_mst] OUTT