invalid use of group error

[TheJuan]

hi

i really spent my time on this one.

 

SELECT tblinitemdetail.QuantityIn, tblinitemdetail.UnitUsed, tbltransactioninid.`Date`,

tblitemid.ProductName, tblinitemdetail.ItemDetailID, SUM(tblrequestdetail.QuantityOut) AS Total_Out,

tblinitemdetail.QuantityIn - SUM(tblrequestdetail.QuantityOut) AS Balance,

tblrequestdetail.QuantityOut FROM tblrequestdetail

INNER JOIN tblinitemdetail ON (tblrequestdetail.ItemDetailID = tblinitemdetail.ItemDetailID)

INNER JOIN tbltransactioninid ON (tblinitemdetail.TransactionInID = tbltransactioninid.TransactionInID)

INNER JOIN tblitemid ON (tblinitemdetail.ItemID = tblitemid.ItemID)

WHERE (tblinitemdetail.QuantityIn - SUM(tblrequestdetail.QuantityOut)) <= '10'

GROUP BY tblinitemdetail.QuantityIn, tblinitemdetail.UnitUsed, tbltransactioninid.`Date`,

tblitemid.ProductName, tblinitemdetail.ItemDetailID, tblrequestdetail.QuantityOut

ORDER BY tblinitemdetail.QuantityIn - SUM(tblrequestdetail.QuantityOut) ASC

 

everything is fine with out this line:

WHERE (tblinitemdetail.QuantityIn - SUM(tblrequestdetail.QuantityOut)) <= '10'

[fenway]

You can't use an aggregate expression in the WHERE clause -- move it to a HAVING clause.