Solved

SUM qty in a select statement not working

Posted on 2013-05-28
18
344 Views
Last Modified: 2013-05-28
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
0
Comment
Question by:razza_b
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 4
  • 4
  • +1
18 Comments
 
LVL 9

Assisted Solution

by:sarabhai
sarabhai earned 200 total points
ID: 39201046
try this one and let me know what happen

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(KV1.Qty) from kitverify KV1 where TB.Job = KV1.[WORKORDER]  )
 WHERE ((KV.[WORKORDER] IS NULL)  
   AND (KV.[PARTNUMBER]) IS NULL  
   AND (TB.Job = 'PMM1320002'))
0
 
LVL 1

Author Comment

by:razza_b
ID: 39201091
it brings me back all of my 13 rows now, because i have entered in data for 12 rows i should only be getting one row back.
0
 
LVL 22

Expert Comment

by:Thomasian
ID: 39201136
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])

Open in new window

0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 39201144
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?
0
 
LVL 1

Author Comment

by:razza_b
ID: 39201159
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'))
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39201169
I would suggest you try the suggestion by: Thomasian above
I think this meets your need.
0
 
LVL 1

Author Comment

by:razza_b
ID: 39201172
PortletPaul i think you could be correct as i get my one row back but unsure why in needs to be <= ??
0
 
LVL 1

Author Comment

by:razza_b
ID: 39201273
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
0
 
LVL 1

Author Comment

by:razza_b
ID: 39201296
is there a way i can add to that query if its null make it 0?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39201305
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' 

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39201308
not I don't know if you really need to do that sum()
0
 
LVL 22

Expert Comment

by:Thomasian
ID: 39201328
Can you post some sample data with the expected result?
0
 
LVL 1

Author Comment

by:razza_b
ID: 39201334
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'))
0
 
LVL 1

Author Comment

by:razza_b
ID: 39201353
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)
0
 
LVL 22

Accepted Solution

by:
Thomasian earned 300 total points
ID: 39201387
You mentioned earlier that the query does not work when qty is null on table kitverify. You can try adding ISNULL to return 0 instead. Try if this works
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])

Open in new window

0
 
LVL 1

Author Comment

by:razza_b
ID: 39201485
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'))
0
 
LVL 22

Expert Comment

by:Thomasian
ID: 39201501
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..
0
 
LVL 1

Author Comment

by:razza_b
ID: 39201527
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.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question