August 25, 2015 at 11:07 am
There are 3 columns in the resultset of below code. part num, Qty and MO num. Each MOnum has different 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
August 25, 2015 at 11:31 am
we need sample data to be able to help you.
Create table ...
insert into ...
and select statement that you're trying and the wanted result.
thanks
JG
August 25, 2015 at 11:33 am
It looks like you need a GROUP BY clause, but I can't see your table definitions from your description. Please post DDL and some consumable data. If you need more info on how, please see the link in my signature.
August 25, 2015 at 11:47 am
I agree with Ed, You might only need a SUM() with GROUP BY.
It seems that you're not using your derived table "labor", so you might be able to remove it.
Do you have a CLR function called LIST? Or are you using something different from SQL Server? Or do you need help with that concatenation as well?
August 25, 2015 at 3:31 pm
I am using data tab in fishbowl inventory. Firebird sql. Database is connected to this software. Btw, labor isnt being used. I have removed it. Can you edit below code with Sum function of quantity(woitemqty)?
part.num , (woitem.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
August 25, 2015 at 3:42 pm
chindilog (8/25/2015)
I am using data tab in fishbowl inventory. Firebird sql. Database is connected to this software. Btw, labor isnt being used. I have removed it. Can you edit below code with Sum function of quantity(woitemqty)?part.num , (woitem.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
Well, that explains the LIST function that neither Luis or I recognized. This is a SQL Server forum that deals with T-SQL. I know SQL Server and Oracle, but I've never heard of Firebird SQL until now. You'll probably get better results by posting to a forum dedicated to your database platform.
That being said, I'll take a guess at it. Keep in mind that this is only a guess.
SELECT part.num, SUM(woitem.qtytarget) Total
FROM wo
INNER JOIN moitem ON moitem.id = wo.moitemid
GROUP BY part.num;
I don't see where part.num comes from. I'm guessing that it's something related to the LIST function, which I don't know. So, you'll have to figure out how to pull it in as you need it. There may be more I left out because I don't know your table structure, but you can probably adapt the T-SQL above to work on your platform. Most SQL is similar and there's nothing here that's specific to T-SQL.
I hope this helps.
August 25, 2015 at 6:26 pm
yeah thats something from the subquery list. I can use T-sql. Can you help me with that. Also in concatenation?
Thanks. Btw, sum() didnt work.
August 25, 2015 at 7:52 pm
Since this is a guessing game without the CREATE TABLE and INSERT scripts and the expected results, please provide them. I'm just not a fan of pin the tail on the donkey.
August 26, 2015 at 5:28 am
chindilog (8/25/2015)
yeah thats something from the subquery list. I can use T-sql. Can you help me with that. Also in concatenation?Thanks. Btw, sum() didnt work.
It doesn't work or you don't have the SUM() function available? If it isn't a function, then I don't know how much help we're going to be on concatenation. I'd suggest you post your question to a forum that specialized in Firebird SQL.
pietlinden (8/25/2015)
Since this is a guessing game without the CREATE TABLE and INSERT scripts and the expected results, please provide them. I'm just not a fan of pin the tail on the donkey.
I was guessing in my previous post in the hopes that the general format could be used to build a statement that works, but not knowing the table structure, LIST command or even the variant of SQL, I think I'm dead in the water. Please see the link in my signature for how to post consumable DDL and data and I'll give it another shot.
August 26, 2015 at 8:35 am
From a quick search on the firebird documentation, which almost made me believe that it wouldn't have a SUM() function, this seems to be close to what you need.
SELECT part.num ,
SUM(woitem.qtytarget) AS woitemqty,
LIST(wo.num, ',') 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 part ON woitem.partid = part.id
GROUP BY part.num, mo.id
On T-SQL, the concatenation could be something like this:
SELECT part.num ,
SUM(woitem.qtytarget) AS woitemqty,
STUFF((SELECT ',' + wo.num
FROM wo
INNER JOIN moitem ON wo.moitemid = moitem.id
WHERE moitem.moid = mo.id
FOR XML PATH(''),TYPE).value('.', 'varchar(max)'),1,1,'') 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 part ON woitem.partid = part.id
GROUP BY part.num, mo.id
The code might have errors because I can't test it without sample data.
EDIT: To understand the concatenation in T-SQL, check the following article: http://www.sqlservercentral.com/articles/comma+separated+list/71700/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply