SQLTeam.com | Weblogs | Forums

Where clause in the query

I could group by Qty but not woitempart. Tried to use where clause but in vain. Need a result set by woitempart (Column). Example: Where woitempart = xxxx

SELECT

part.num AS woitempart, (woitem.qtytarget/wo.qtytarget) AS woitemqty,

(SELECT LIST(wo.num, ',') FROM wo INNER JOIN moitem ON wo.moitemid = moitem.id WHERE moitem.moid = mo.id) AS wonums
FROM mo INNER JOIN moitem ON mo.id = moitem.moid

LEFT JOIN wo ON moitem.id = wo.moitemid

LEFT JOIN woitem ON wo.id = woitem.woid AND woitem.typeid = 10
LEFT JOIN (Select sum(woitem.qtytarget) as labor, woitem.woid, uom.code as uom
from woitem
JOIN part on woitem.partid = part.id and part.typeid = 21
JOIN uom on woitem.uomid = uom.id
group by 2,3) as labor on wo.id = labor.woid
LEFT JOIN part ON woitem.partid = part.id
Where woitem.qtytarget < 1200 order by woitempart ASC

Which database server?
to my knowledge list function is not in mssql.

SqlServer 2008

You should use the field name from the table in your where statement.
In this case:
where part.num = ?????

Field name is Part.num. It doesnt work

If the query you posted works, you alias the field "part.num" as woitempart:

SELECT part.num AS woitempart

and thus you should be able to filter on part.num (where sentence).
What error do you get?

I have removed Alias and used only part.num and its working now. Btw, I have duplicate part numbers and quantities are different. If I want to remove duplicates and add all the quantities of specific part numbers,

Please provide sample data, table descriptions (create statements), your query and the expected output (from the sample data you provided).
See here.

Tables are NUM, WOITEMQTY and WONUMS.
Query is

SELECT 
    
     part.num , (woitem.qtytarget/wo.qtytarget) AS woitemqty,
     
    (SELECT LIST(wo.num, ',') FROM wo INNER JOIN moitem ON wo.moitemid = moitem.id WHERE moitem.moid = mo.id) AS wonums
   

FROM mo
    INNER JOIN moitem ON mo.id = moitem.moid
    
    LEFT JOIN wo ON moitem.id = wo.moitemid
           
    LEFT JOIN woitem ON wo.id = woitem.woid AND woitem.typeid = 10
    LEFT JOIN (Select sum(woitem.qtytarget) as labor, woitem.woid, uom.code as uom
                    from woitem
	          JOIN part on woitem.partid = part.id and part.typeid = 21
                    JOIN uom on woitem.uomid = uom.id
                  group by 2,3) as labor on wo.id = labor.woid
    LEFT JOIN part ON woitem.partid = part.id
Where part.num = 'W00015'

With this query I can get all partnumbers W00015 and its quantities. I want to sum up all the quantities for W00015.