SQLTeam.com | Weblogs | Forums

Sum the duplicates and add new Entity

tsql
sql2008

#1

There are 3 columns part num, Qty and MO. Each MO has part numbers.So there might be same part numbers in MO's. Each part num has qty. So, if I group by part num, I get Qty.

1.There are duplicates of part.num and I want to remove duplicates and add quantities of those duplicates into one single quantity. For example, xxxx is a part num, then xxxx=1,xxxx=3,xxxx=5. I want xxxx=9. I want to sum those. Another question is, each MO has a user. I want to join user and MO num in MO.

Heres the code,

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


Combine 2 select statements
#2

I read your post couple of times, but could not understand what you are trying to get. The query you posted does not parse, so that is not of much help either.

Ideally, you should post the DDL for a sample table, along with data to populate that table, and your desired output. For example, like this:

-- Sample table
CREATE TABLE #Parts
(
	PartNum INT,
	Qty INT,
	MO VARCHAR(32)
);
-- Sample Data
INSERT INTO #Parts
        ( PartNum, Qty, MO )
VALUES
	(1, 17, 'A'),
	(1, 11, 'A'),
	(2,  3, 'X');
	
----------------------------
-- desired output
CREATE TABLE #Output
(
	PartNum INT, 
	Qty INT,
	MO VARCHAR(32)
);
INSERT INTO #Output
        ( PartNum, Qty, MO )
VALUES
	(1, 28, 'A'),
	(2,  3, 'B');

-- this is the output I am looking for
SELECT * FROM #Output;

BTW, you can format code by selecting the text you want to format as code and clicking the </> button at the top of the edit window.