Error - n aggregate may not appear in the WHERE clause

Ramco Ramco

3y945SQL

On below code i am getting error - An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

SELECT T1.ITEMCODE, T1.QTY, T2.QUANTITY FROM dbo.OW T0 INNER JOIN dbo.WO T1

ON T0.DOCENTRY = T1.DOCENTRY LEFT OUTER JOIN dbo.IG T2 ON T2.BASEENTRY = T0.DOCENTRY AND T1.ITEMCODE = T2.ITEMCODE 

WHERE T1.PLANNEDQTY > sum((ISNULL(T2.QUANTITY, 0))) AND T0.DOCENTRY = 1506

Thanks

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty3y ago

To filter rows based on aggregated values, you usually employ the HAVING clause after a GROUP BY.

         SELECT T1.ITEMCODE, T1.QTY, T2.QUANTITY
           FROM dbo.OW T0
     INNER JOIN dbo.WO T1 ON T0.DOCENTRY = T1.DOCENTRY
LEFT OUTER JOIN dbo.IG T2 ON T2.BASEENTRY = T0.DOCENTRY AND T1.ITEMCODE = T2.ITEMCODE
          WHERE T0.DOCENTRY = 1506
       GROUP BY T1.ITEMCODE, T1.QTY, T2.QUANTITY, T1.PLANNEDQTY
         HAVING T1.PLANNEDQTY > SUM(ISNULL(T2.QUANTITY, 0))