SUM qty in a select statement not working
Posted on 2013-05-28
im needing a little bit of help with this query i should be getting 1 row returned and i get none and the req qty is greater than, so im trying to add this -> TB.ReqQty > SUM(KV.Qty) but im getting error msg - An aggregate cannot appear in an ON clause unless it is in a subquery contained in a HAVING clause or select list, and the column being aggregated is an outer reference.
is there a way i can get the sum to work?
TB.Job as 'JOB'
, TB.Component as 'SERIAL'
, TB.ReqQty as 'QTY'
select row_number() over (partition by Job,Component ORDER BY ReqQty) rownum, *
from TblBOM WITH (NOLOCK)) TB
LEFT JOIN (
select row_number() over (partition by WORKORDER,PARTNUMBER ORDER BY Qty) rownum, *
from kitverify) KV ON ((TB.Job = KV.[WORKORDER])
AND TB.Component = KV.[PARTNUMBER])
AND KV.rownum = TB.rownum
AND TB.ReqQty > SUM(KV.Qty)
WHERE ((KV.[WORKORDER] IS NULL)
AND (KV.[PARTNUMBER]) IS NULL
AND (TB.Job = 'PMM1320002'))