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.
SELECT AVG(COUNT(royalty)) RoyaltyAvg
You can get around this problem by breaking out the computation of the average in derived tables.
SELECT COUNT(royalty) AS RoyaltyCounts
Reference : Pinal Dave (https://blog.sqlauthority.com) , BOL.
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!.
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…
WHERE NOT EXISTS
(SELECT OfficeRegId FROM [LabourDept].[dbo].[AbhidayMaster]
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