CODEWITHSUNDEEP
sql-server
Shail
Shail

Reputation: 29

An aggregate may not appear in the WHERE clause unless it is in

select 
    * 
from sap_stockmaster 
where itemid=9893 
and salemrp=1034.00 
and salemrp in(
    select 
        salemrp 
    from sap_stockmaster 
    where SUM(stkqty)>0
) 
order by salemrp desc

I want record whose Sum of Quantity is greater than Zero but get the following 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.

Upvotes: 1

Views: 6672

Answers (1)

Igor
Igor

Reputation: 62238

When filtering on aggregates you should use having and not where

select * 
from sap_stockmaster 
where itemid=9893 
and salemrp=1034.00 
and salemrp in (select salemrp 
    from sap_stockmaster 
    group by salemrp
    HAVING SUM(stkqty)>0) 
order by salemrp desc

Also this is probably more performant written as either a join or using EXISTS

-- example with Exists

SELECT * 
FROM sap_stockmaster ss1
WHERE itemid=9893 
AND salemrp=1034.00 
AND EXISTS (SELECT 1 
    FROM sap_stockmaster ss2
    WHERE ss1.salemrp = ss2.salemrp
    GROUP BY ss2.salemrp
    HAVING SUM(ss2.stkqty)>0)
ORDER BY salemrp DESC

Upvotes: 3

Related Questions