razza_b
asked on
SUM qty in a select statement not working
Hi
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?
SELECT
TB.Job as 'JOB'
, TB.Component as 'SERIAL'
, TB.ReqQty as 'QTY'
FROM (
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'))
thanks
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?
SELECT
TB.Job as 'JOB'
, TB.Component as 'SERIAL'
, TB.ReqQty as 'QTY'
FROM (
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'))
thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try
SELECT
TB.Job AS 'JOB'
,TB.Component AS 'SERIAL'
,TB.ReqQty as 'QTY'
FROM
TblBOM TB
WHERE
TB.Job = 'PMM1320002' AND
TB.ReqQty > (SELECT SUM(Qty) FROM kitverify WHERE TB.Job = [WORKORDER] AND TB.Component = [PARTNUMBER])
I had a query prepared to enter here, then went back over it and, well I need to check
I think you are trying to match a job to a workorder through a partnumber.
the workorder should have a smaller (or equal?) qty to the job requirement.
is this correct?
I think you are trying to match a job to a workorder through a partnumber.
the workorder should have a smaller (or equal?) qty to the job requirement.
is this correct?
ASKER
i get the 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.
but i have tried this and get my one row back but still unsure as the req qty should be greater than...
SELECT
TB.Job as 'JOB'
, TB.Component as 'SERIAL'
, TB.ReqQty as 'QTY'
FROM (
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 <= ( select SUM(KV.Qty) from kitverify KV where TB.Job = KV.[WORKORDER] and TB.Component = KV.[PARTNUMBER] ) WHERE ((KV.[WORKORDER] IS NULL)
AND (KV.[PARTNUMBER]) IS NULL
AND (TB.Job = 'PMM1320002'))
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.
but i have tried this and get my one row back but still unsure as the req qty should be greater than...
SELECT
TB.Job as 'JOB'
, TB.Component as 'SERIAL'
, TB.ReqQty as 'QTY'
FROM (
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 <= ( select SUM(KV.Qty) from kitverify KV where TB.Job = KV.[WORKORDER] and TB.Component = KV.[PARTNUMBER] ) WHERE ((KV.[WORKORDER] IS NULL)
AND (KV.[PARTNUMBER]) IS NULL
AND (TB.Job = 'PMM1320002'))
I would suggest you try the suggestion by: Thomasian above
I think this meets your need.
I think this meets your need.
ASKER
PortletPaul i think you could be correct as i get my one row back but unsure why in needs to be <= ??
ASKER
i tried thomasian 2nd query and it didnt return anything, i think its because its null in kitverify table(but when it does have a qty it will return but only if it has an actual qty less than the TB.ReqQty
ASKER
is there a way i can add to that query if its null make it 0?
try something like this perhaps
SELECT
TB.Job AS 'JOB'
, TB.Component AS 'SERIAL'
, TB.ReqQty as 'QTY'
, case when TB.ReqQty <= KT.sumqty then WORKORDER else 'No Workorder Matches' end as matching
FROM TblBOM TB
left join (
select [WORKORDER] , [PARTNUMBER], sum(qty) over (partition by [WORKORDER] , [PARTNUMBER]) as sumqty
FROM kitverify
) KT ON TB.Job = KT.[WORKORDER] AND TB.Component = KT.[PARTNUMBER]
WHERE TB.Job = 'PMM1320002'
not I don't know if you really need to do that sum()
Can you post some sample data with the expected result?
ASKER
this seems to be the only one that returns the one row but dont get how it works when it is <=
SELECT
TB.Job as 'JOB'
, TB.Component as 'SERIAL'
, TB.ReqQty as 'QTY'
FROM (
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 <= ( select SUM(KV.Qty) from kitverify KV where TB.Job = KV.[WORKORDER] and TB.Component = KV.[PARTNUMBER] ) WHERE ((KV.[WORKORDER] IS NULL)
AND (KV.[PARTNUMBER]) IS NULL
AND (TB.Job = 'PMM1320002'))
SELECT
TB.Job as 'JOB'
, TB.Component as 'SERIAL'
, TB.ReqQty as 'QTY'
FROM (
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 <= ( select SUM(KV.Qty) from kitverify KV where TB.Job = KV.[WORKORDER] and TB.Component = KV.[PARTNUMBER] ) WHERE ((KV.[WORKORDER] IS NULL)
AND (KV.[PARTNUMBER]) IS NULL
AND (TB.Job = 'PMM1320002'))
ASKER
JOB SERIAL QTY
PMM1320002 MT41K256M8DA125K 36666
thats the output so when my data being entered eventually reaches that qty or more then no rows left (its a multi serial scan)
PMM1320002 MT41K256M8DA125K 36666
thats the output so when my data being entered eventually reaches that qty or more then no rows left (its a multi serial scan)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
its weird i get the same output from both these queries...
SELECT
TB.Job AS 'JOB'
,TB.Component AS 'SERIAL'
,TB.ReqQty as 'QTY'
FROM
TblBOM TB
WHERE
TB.Job = 'PMM1320002' AND
TB.ReqQty > (SELECT ISNULL(SUM(Qty),0) FROM kitverify WHERE TB.Job = [WORKORDER] AND TB.Component = [PARTNUMBER])
SELECT
TB.Job as 'JOB'
, TB.Component as 'SERIAL'
, TB.ReqQty as 'QTY'
FROM (
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 <= ( select SUM(KV.Qty) from kitverify KV where TB.Job = KV.[WORKORDER] and TB.Component = KV.[PARTNUMBER] ) WHERE ((KV.[WORKORDER] IS NULL)
AND (KV.[PARTNUMBER]) IS NULL
AND (TB.Job = 'PMM1320002'))
SELECT
TB.Job AS 'JOB'
,TB.Component AS 'SERIAL'
,TB.ReqQty as 'QTY'
FROM
TblBOM TB
WHERE
TB.Job = 'PMM1320002' AND
TB.ReqQty > (SELECT ISNULL(SUM(Qty),0) FROM kitverify WHERE TB.Job = [WORKORDER] AND TB.Component = [PARTNUMBER])
SELECT
TB.Job as 'JOB'
, TB.Component as 'SERIAL'
, TB.ReqQty as 'QTY'
FROM (
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 <= ( select SUM(KV.Qty) from kitverify KV where TB.Job = KV.[WORKORDER] and TB.Component = KV.[PARTNUMBER] ) WHERE ((KV.[WORKORDER] IS NULL)
AND (KV.[PARTNUMBER]) IS NULL
AND (TB.Job = 'PMM1320002'))
Is the output correct?
If not, can you some sample data and the desired output? I don't really understand why you need to get a row number for each table..
If not, can you some sample data and the desired output? I don't really understand why you need to get a row number for each table..
ASKER
yes output looks correct and i like the query you supplied is much simpler and quicker.
i cant actually remember why rownum is being used think it was just to get remaining rows but seems to be long winded way.
i cant actually remember why rownum is being used think it was just to get remaining rows but seems to be long winded way.
ASKER