Sum one row from joined table

tosa · #1 (**permalink**) 11-04-10, 08:11

Hi!

I have this SQL so far:
SELECT (SUM(TotalQty) - SUM(ProdQty)) AS 'openProd' FROM PUB.ProdOper INNER JOIN PUB.ProdOrder ON (ProdOper.PrOrderNum=ProdOrder.PrOrderNum) WHERE ProdOrder.ItemCode = '1234' AND ProdOrder.Completed = 0

The problem is I want to sum only the last row from table ProdOper (where ProdOper.PosNum is the highest). How can I do that?

Thanks!

tosa · #2 (**permalink**) 11-05-10, 02:59

I thought I'd explain it a little better:

There are tables ProdOrder and ProdOper.

ProdOrder
=======
OrderNum
1001
1002

ProdOper
=======
OrderNum, PosNum
1001, 500
1001, 510
1002, 600
1002, 610

How do I select, from ProdOper, only the rows with the highest PosNum? It's a Progress database.

dav1mo · #3 (**permalink**) 11-05-10, 13:39

then add in a subselect that you a.Posnum = (select max(Posnum) from table b where a.Ordernum = b.Ordernum)
Dave

tosa · #4 (**permalink**) 11-08-10, 03:17

Thanks for your reply.

I didn't get it to work though.

I don't get it, this query

"SELECT * FROM PUB.ProdOper AS l INNER JOIN PUB.ProdOrder AS o ON l.PrOrderNum=o.PrOrderNum WHERE o.ItemCode = '2073' AND l.Completed = 0 AND o.Completed = 0 AND l.OperPosNum = (SELECT MAX(OperPosNum) FROM PUB.ProdOper)"

returns no rows at all, but this

"SELECT * FROM PUB.ProdOper AS l INNER JOIN PUB.ProdOrder AS o ON l.PrOrderNum=o.PrOrderNum WHERE o.ItemCode = '2073' AND l.Completed = 0 AND o.Completed = 0 AND l.OperPosNum = '515'"

returns two rows.

r937 · #5 (**permalink**) 11-08-10, 04:58

Code:

SELECT something
     , anything
     , just_not_the_dreaded_evil_select_star
  FROM PUB.ProdOrder AS o
INNER 
  JOIN ( SELECT PrOrderNum
              , MAX(OperPosNum) AS last_posnum
           FROM PUB.ProdOper
          WHERE Completed = 0
         GROUP
             BY PrOrderNum ) AS m
    ON m.PrOrderNum = o.PrOrderNum 
INNER
  JOIN PUB.ProdOper AS l 
    ON l.PrOrderNum = o.PrOrderNum 
   AND l.OperPosNum = m.last_posnum
 WHERE o.ItemCode = '2073' 
   AND o.Completed = 0

tosa · #6 (**permalink**) 11-08-10, 08:44

Thanks Rudy! This looks quite close.

I'd like to select the rows from table ProdOper with highest OperPosNum, but I'm not sure how to modify your query to do it...

r937 · #7 (**permalink**) 11-08-10, 08:59

what makes you think my query needs to be modified?

did you test it?

tosa · #8 (**permalink**) 11-08-10, 09:19

I'm terribly sorry!

Eeh...I used the "evil" star again and didn't notice the stuff I actually wanted was added at the end.

Thanks again! You're a pro!

Internet Services

Web Development

Digital Marketing

Consumer Tech