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

1
votes
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.

1
sql-server
Since you're quoting the error message verbatim, please explain what part of it it was that you didn't understand, because so far, all we can do is tell you that 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. - Lasse V. Karlsen
change where to having and precede it with a group by salemrp on the subquery. - xQbert
How can you get "Sum of Quantity" for a single record? Did you mean where stkqty > 0 ? - Lasse V. Karlsen
Only select the columns you want, group by the columns that are not aggregated and put the SUM(stkqty) > 0 in the Having clause. - SS_DBA

1 Answers

3
votes

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