Where clause in the query

Transact-SQL
,
1

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

2

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

3

SqlServer 2008

4

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

5

Field name is Part.num. It doesnt work

6

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?

7

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,

8

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

9

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.